how to ignore the values before and after between the rows in a table

I have a table as like below I have to add a new column to the table as “value_”, which should contains age values for rows between last TRUE i.e row number 4 (highlighted in green color) …

Aggregating data in BigQuery by month

I feel silly asking this because it seems simple, but I concede and need some help. In BigQuery I have a Table that looks like this: Date Portfolio Super_Discipline Dollars Units 2020-05-20 …

Statistical functions on columns of arrays in BigQuery

If I have data that looks like the follows: +——+————+——-+ | a | val | val2 | +——+————+——-+ | 3.14 | [1, 2, 3] | [2, 3]| | 1.59 | [7, 8, 9] | … | | -1 …

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 …

parse_timestamp vs format_timestamp bigquery

Could someone help me understand why these two queries are returning different results in bigquery? select FORMAT_TIMESTAMP(‘%F %H:%M:%E*S’, “2018-10-01 00:00:00” , ‘Europe/London’) returns …

DATE_DIFF() in BigQuery to calculate time between rows

I would like to calculate the time delay between several customer purchases. However, each purchase is saved in an individual row. The data set looks similar to the following: customer | order_id | …

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 …

Condition OFFSET on SPLIT in SQL

I am using SQL on Google Big Query. I have a column MyColumn which value is in this format : Text0;Text1;Text2;Text3;Text4;T12=12/T26=5/T13=1/T55=ABC I need to fetch the value of T13 (“1” …