Rolling n-day aggregation conditional on another column

Question

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!

user_5221944 · Answer

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

Rolling n-day aggregation conditional on another column

1 Answers