1. Home

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/…

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 …

Calculate order sequence in BigQuery

I am new to BigQuery SQL and struggling with calculating an “order sequence” from a table of customer orders. In my example, I am working with customer purchases, where I have the sales …

How to check if several values are in a list in BigQuery

Is there a way to make this clause more compact? SELECT campaign ,event_list FROM `adobe_analytics.raw_data_20200*` WHERE campaign IS NOT NULL — What does it mean when a campaign is null in …

How I can select random amount of rows based on varios criterias?

I have a table that has 3 main categories: age, city and gender. The gender column is boolean. The age category is in the following range: [’18-25′,’26-35′,’36-45′,’46-55′,’56-65′,’66-75′,’75+’] The …

How to fill irregularly missing values with linear interepolation in BigQuery?

I have data which has missing values irregulaly, and I’d like to convert it with a certain interval with liner interpolation using BigQuery Standard SQL. Specifically, I have data like this: # data is …

BigQuery – JOIN on two tables using string and array

We have two tables in BigQuery like below: Table A Name | Question | Answer —–+———–+——- Bob | Interest | a Bob | Interest | b Sue | Interest | a Sue | Interest | c Joe | …

How to calculate weekly and monthly appearances of distinct string values in SQL Google Big Query?

I am new to SQL, and I have a dataset with date values and domain column. Domain column only contains values ‘personal’ and ‘business’. What I am trying to accomplish is to calculate weekly and …

Is there any way i can populate entity based on months but excluding the first month each loops?

i have a set of data sample like this | id | month | x | jan | x | feb | x | mar is there any way i can populate that sample to be like this? | id | month | number | x | jan | 1 | x | feb | …