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):

I’d appreciate any tips, thanks in advance!