I have the following 2 tables: tab1 with 37146 rows week_ref with 730 rows All I want to do is join those tables on year and week so that the first week day and last week day will display next to …
Tag: google-bigquery
Is there a way to change this BigQuery self-join to use a window function?
Let’s say I have a BigQuery table “events” (in reality this is a slow sub-query) that stores the count of events per day, by event type. There are many types of events and most of them don’t occur on …
Isolate/Sum Data Conditionally Based On Event Sequence
I have a table that tracks events sequentially and calculates the time for each event. What I want to do using the below sample data, is calculate the total number of Time Elapse (Seconds) of all …
avoid duplication when joining tables without unique id using foreign keys
I’m facing this issue where I don’t really know how to handle duplicate rows when joining two tables. I have two tables I’d like to join Value_x table : ID Campaign Value_x foo …
RANK() function with over is creating ranks dynamically for every run
I am creating ranks for partitions of my table. Partitions are performed by name column with ordered by its transaction value. While I am generating these partitions and checking count for each of the …
Need help joining 2 tables and rolling them up in a unique way using BigQuery SQL
I have 2 tables at different granularity in a BigQuery dataset. I need to join those two and roll it up using BigQuery SQL in such a way that the value in one of the columns of the 2nd table becomes …
Adding missing date rows to a BigQuery Table
I have a table where 1 of the rows is an integer that represents the rows time. Problem is the table isn’t full, there are missing timestamps. I would like to fill missing values such that every 10 …
Error: No matching signature for function IF for argument types: BOOL. Supported signature: IF(BOOL, ANY, ANY)
I’m trying to count the number of times a “rating” is above 9. Working in Google BigQuery with StandardSQL I previously was using a CASE WHEN function but wanted to change it to an IF as there is …
Transform table to one-hot encoding for many rows
I have a SQL table of the following format: ID Cat 1 A 1 B 1 D 1 F 2 B 2 C 2 D 3 A 3 F Now, I want to create a table with one ID per row, and multiple Cat’s in a row. My …
How To Extract Distinct ID’s Present In One Table But Not Another
I have two tables, one with the sales entries (multiple of the same salesman id number), and another that is a master file of sales people (only one salesman id per each salesman). How can I write a …