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 cost 20 cents for simplicity
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 integers or strings as below. Any
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 running total should start over from
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 firstName and the lastName but ran into an error when there were 2 “,” in