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!
More Stories
Instructions to Purchase Bitcoin – Stage One
The most effective way to find out about bitcoin, is to hop in and get a couple in your "pocket"...
Boost Your Retirement Through Investing Into Cryptocurrency
Across the world, human life expectancy has grown by leaps and bounds. Compared to the 1950s, it has shot up...
What Is Cryptocurrency? Here’s What You Should Know
Cryptocurrency is a type of digital currency that you can use in order to purchase goods and services. For secured...
6 Benefits of Investing in Cryptocurrencies
The birth of bitcoin in 2009 opened doors to investment opportunities in an entirely new kind of asset class -...
How Can You Buy Or Sell Your Own Shiba Inu And Make Profits
Are you a crypto enthusiast and interested in learning more about cryptocurrencies? Then, you should check out the KuCoin Cryptocurrency...
How to Mine Ethereum on a PC?
There are a ton of processes that take place in cryptocurrency, and one of them is cryptocurrency mining. This is...