Reputation: 21
struggling to figure out how to implement a code that will allow me to calculate following (using SQL in BigQuery) in an elegant way.
I'd need to calculate a rolling n-day aggregation (let's assume rolling 3-day sum of units ) for each date but only taking into account data that where the price is less than a certain value (let's assume 50).
So based on below table
date | price | units |
---|---|---|
01-21 | 30 | 200 |
01-22 | 100 | 500 |
01-23 | 20 | 200 |
01-24 | 20 | 100 |
01-25 | 80 | 100 |
01-26 | 40 | 250 |
I'd need my query to return:
date | units |
---|---|
01-21 | 200 |
01-22 | 200 |
01-23 | 400 |
01-24 | 300 |
01-25 | 300 |
01-26 | 350 |
Struggling to figure out how to combine window calculations with the additional conditions.
Thanks in advance!
Upvotes: 1
Views: 166
Reputation: 173210
Consider below approach
select date, sum(if(price < 50, units, 0)) over win units
from your_table
window win as (order by unix_date(date) range between 2 preceding and current row)
if applied to sample data as in your question -
with your_table as (
select date '2022-01-21' date, 30 price, 200 units union all
select '2022-01-22', 100, 500 union all
select '2022-01-23', 20, 200 union all
select '2022-01-24', 20, 100 union all
select '2022-01-25', 80, 100 union all
select '2022-01-26', 40, 250
)
the output is
Upvotes: 1