NatalieGoodman
NatalieGoodman

Reputation: 21

Rolling n-day aggregation conditional on another column

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

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

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

enter image description here

Upvotes: 1

Related Questions