Imagine I have a table like ID Date 1 2021-01-01 1 2021-01-05 1 2021-01-17 1 2021-02-01 1 2021-02-18 1 2021-02-28 1 2021-03-30 2 2021-01-01 2 2021-01-14 2 2021-02-15 I want to select all data on this table, but creating a new column with a new Event_ID. An Event is defined as all the rows with the same ID, wi…
Tag: sliding-window
Data aggregation by sliding time periods
[Query and question edited and fixed thanks to comments from @Gordon Linoff and @shawnt00] I recently inherited a SQL query that calculates the number of some events in time windows of 30 days from a log database. It uses a CTE (Common Table Expression) to generate the 30 days ranges since ‘2019-01-01&#…
BigQuery SQL for 28-day sliding window aggregate (without writing 28 lines of SQL)
I’m trying to compute a 28 day moving sum in BigQuery using the LAG function. The top answer to this question Bigquery SQL for sliding window aggregate from Felipe Hoffa indicates that that you can use the LAG function. An example of this would be: Is there a way to do this without having to write out 2…
Find start and end dates when one field changes
I have this data in a table When ever the value in FIELD_D changes it forms a group and I need the min and max dates in that group. The query shoud return The examples that I have seen so far have the data in Field_D being unique. Here the data can repeat as shown, First it is “N” then