I have the following table. basically simplified version of my table. I need to aggregate few columns, I will explain what I am trying to do and also what I have written till now. tableName variations can be like pizza toppings, each variation has an amount, say veggie toppings cost 10 cent and meat toppings …
Tag: google-bigquery
Identifying products that a customer owns
I want to check whether my customer owns an iPhone, iPad, or Macbook. I am not looking for how many iPhone or iPad or Macbook one customer has, I just want to identify if they own any of them, or if they own any two, or if they own all three. I am thinking of using the CASE WHEN function
String Aggregtion to create pivot columns in Big Query
I am trying to implement this exact solution linked below, but it seems GROUP_CONCAT_UNQUOTED is no longer a valid BQ function. Is there a solution to this that works in 2020? p.s. I would have just …
Duplicate column names in BigQuery when selecting twice within the same table
I am trying to perform the following query in BigQuery: SELECT * FROM ( SELECT date, grob as `grob_r1` FROM `mytab_tbl` WHERE region = “r1” ) AS TAB_1, ( SELECT …
Clean way to construct cartesian joins in BigQuery (SQL)
I have multiple lists, and I want to construct cartesian joins of these lists using SQL. I’m wondering if you know a cleaner method than the one below. Thanks Answer Your query looks fine, but there is no reason to use CTEs:
Running total with several conditions in bigquery
I need to calculate a running total but need to reset the total on a condition (when expected reached = 0 and product_group and product changes). Got help here without the two extra fields: Calculate a running total with a condition in BigQuery I have this table and may use product_group and product as intege…
Calculate a running total with a condition in BigQuery
Sorry bad topic… I need to calculate a running total but need to reset the total on a condition (when expected reached = 0). I have this table: Expected result with running total – the condition is that while “Expected Reached” <> 0 running total should be calculated. If “Expected Reached” = 0 the…
If statement Big Query
I’m quite new user of big query with basic SQL skills. I would like to build a query that is using a conditional IF statement to add a new value in one of my field Here is the data that i have …
How do I get all timestamps associated with an ID and aggregate it them into total time elapsed?
I have a sample table: id timestamp 1 2020-02-03T19:25:41.961Z 1 2020-02-03T19:25:42.000Z 1 2020-02-03T19:26:33.147Z 2 2020-02-03T20:21:29.684Z 2 2020-02-03T20:21:29.705Z …
Spliting Name in Google BigQuery
Split FullName into firstName and lastName. I want to split the full name based on “,” into firstName and LastName. anything before “,” should be selected as firstName and anything “,” should be the lastName. I used split(fullName) function and used offset to grab the first…