SQL: Rolling sum in the last 30 days by groups
amazon-redshift, sql
Solution
This is one way to do it using `self join`. Each date is joined with all the dates whose datediff is >0 and <= 30. Thereafter, it is just a grouping operation.
select a1.custid, a1.dt, a1.sales+sum(coalesce(a2.sales,0)) total
from atable a1
left join atable a2 on a1.custid=a2.custid
and datediff(day,a2.dt,a1.dt)<=30 and datediff(day,a2.dt,a1.dt)>0
group by a1.custid,a1.dt,a1.sales
order by 1,2
`Sample Demo in Postgres`
To understand it better, look at the query result of self-join using
select a1.*,a2.*
from atable a1
left join atable a2 on a1.custid=a2.custid
and datediff(day,a1.dt,a2.dt)<=30 and datediff(day,a1.dt,a2.dt)>0
Problem
I have a table as below: ``` date, custid, sales 2015-01-01, 01, 100 2015-01-10, 01, 200 2015-02-05, 01, 300 2015-03-02, 01, 400 2015-03-03, 01, 500 2015-01-01, 02, 100 2015-01-10, 02, 200 2015-02-05, 02, 300 2015-03-02, 02, 400 2015-03-03, 02, 500 ... ``` How can I generate the rolling sum of sales of last 30 days by date and by custid. The desired output would be: ``` date, custid, running_30_day_sales 2015-01-01, 01, 100 2015-01-10, 01, 300 --(100+200) 2015-02-05, 01, 500 --(200+300) 2015-03-02, 01, 700 --(300+400) 2015-03-03, 01, 1200 -- (300+400+500) 2015-01-01, 02, 100 2015-01-10, 02, 300 --(100+200) 2015-02-05, 02, 500 --(200+300) 2015-03-02, 02, 700 --(300+400) 2015-03-03, 02, 1200 -- (300+400+500) ```