I am looking for a query that can aggregate data on a 21 days back basis in the following way:
my table has the following columns:
accountid, date, measure
I need, for each account, date to sum(measure) for the previous 21 days back.
Any idea how to do it in pure SQL without window/analytic function ?
(I'm writing SQL inside a BI product which does not have support in analytic functions)
A rather inefficient method uses correlated subqueries. If you want for each entry the previous 21 days, then:
select t.*, (select sum(t2.measure) from t t2 where t2.accountid = t.accountid and t2.date > t.date - interval '21' day ) as sum21 from t;
Not that date functions differ by database, so your particular database might have another method of subtracting 21 days.