Data Table: Dates Customer Subscription 20/02/2020 A free 21/02/2020 A free 22/02/2020 A free 23/02/2020 B free 23/03/2020 A full 01/03/2020 B full 01/03/2020 A full 02/03/2020 A full Need to fill gaps in dates by the value in the previous date Output: Dates Customer Last Subscription 20/02/2020 A free 21/02/…
Tag: google-bigquery
BigQuery SQL : Dynamically Concat all rows in column by key
I have a table like this: Customer Plan/Date A free (20/01/2020-01/02/2020) A starter (01/02/2020-01/03/2020) A full (01/03/2020) B trial (02/03/2020-05/04/…
Is there a way to filter rows in BigQuery by the contents of an array?
I have data in a BigQuery table that looks like this: My question is, how can I find all rows where “key” = “a”, “value” = 1, but also “key” = “b” and “value” = 3? I’ve tried various forms of using UNNEST but I haven’t been ab…
Use SQL to ensure I have data for each day of a certain time period
I’m looking to only select one data point from each date in my report. I want to ensure each day is accounted for and has at least one row of information, as we had to do a few different things to move a large data file into our data warehouse (import one large Google Sheet for some data, use Python
I need to group by year and month from a timestamp structured as yyyyMMddHHmmss 20170227141500
I need to group by year and month from a timestamp, structured as yyyyMMddHHmmss 20170227141500 (Google’s bigquery gkg table). I need to count the year monthly records. Data type is integer. timestamp is the column name. In fact, I need to gruop by first 6 digits of the timestamp. yearmonth is my group …
Counting events with a specified event.value.string_value in big query
This is a follow up question to this question I have a query: Which returns: I now want to update the query to only count those purchases that have That event is stored like so: I added: Is this the correct way to count only those events that has a specified event_params.value.string_value? I am not sure abou…
Is there a wait method for Google BigQuery SQL
I’m scheduling a query to run every day that picks up data from a table that get’s written to Google BigQuery automatically. Usually the table is there, but I’d like to be sure before I execute the query depending on this table. I’m looking into other ways as well, but the simplest way…
Efficient Multiple Group-bys
I have the following table: Year Week Day_1 Day_2 Day_3 2020 1 Walk Jump Swim 2020 3 Walk Swim Walk 2020 1 Jump Walk Swim I want to group by YEAR, WEEK and Event (Walk, jump, Swim) and count the …
SQL to get 3 adjacent actions without duplicate from the flags
I have a question that a little bit similar with question#66044663 but more complicated. Here’s my dummy data. I want to get 3 adjacent actions(no duplicate) from the flag by each user. Here’s the …
Bigquery split rows by timestamp of event
In Google BigQuery, I have a list of events in a single support session which are tagged by event names. Each support issue resolved_time is the timestamp of the resolved event. Each issue start_time is the first occurrence of either of the events message, pending, or unresolved either at the absolute beginni…