Conditional column in BigQuery that depends on its own value in previous rows and another existing column

I have an SQL table in BigQuery that has:

  • a boolean field, let’s call this field “Trigger
  • a date field where each date occurs only once when partitioned by some other columns in this table, let’s call this field “Date

What I need is to create a conditional boolean column, let’s call it “Alert“, that has a true value when:

  • Trigger is true, and
  • Alert hasn’t been true in the last 3 days, based on Date

Identifying the very first alert for each partition is simple using a window function. The challenge is with all further alerts, because Alert should depend on its own earlier values.

Expected output should be like this (Comment column is added here only for clarity):

Date Trigger Alert Comment
2021-12-01 FALSE 0 Trigger is false
2021-12-02 FALSE 0 Trigger is false
2021-12-03 TRUE 1 No earlier alerts in the last three days
2021-12-04 TRUE 0 Alert during last three days
2021-12-05 TRUE 0 Alert during last three days
2021-12-06 TRUE 0 Alert during last three days
2021-12-07 TRUE 1 No earlier alerts in the last three days
2021-12-08 FALSE 0 Trigger is false
2021-12-09 FALSE 0 Trigger is false
2021-12-10 TRUE 0 Alert during last three days

I’d appreciate any tips, thanks in advance!

Boys Shirts Previous post Boys Shirts
Trading Psychology Tips For Beginners  Next post Trading Psychology Tips For Beginners