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!