Summing data for last 7 day look back window

Question

I want a query that can give result with sum of last 7 day look back.

I want output date and sum of last 7 day look back impressions for each date

e.g. I have a table tblFactImps with below data:

dateFact    impressions id
2015-07-01  4022        30
2015-07-02  4021        33 
2015-07-03  4011        34  
2015-07-04  4029        35
2015-07-05  1023        39
2015-07-06  3023        92
2015-07-07  8027        66
2015-07-08  2024        89

I need output with 2 columns:

dateFact    impressions_last_7

query I got:

select  dateFact, sum(if(datediff(curdate(), dateFact)<=7, impressions,0)) impressions_last_7 from  tblFactImps group by  dateFact;

Thanks!

Can you show us what you have tried so far? – George Bahij Jul 19 '15 at 22:17 — George Bahij, Jul 19 '15 at 22:17

score 1 · Answer 1 · answered Jul 19 '15 at 22:34

If your fact table is not too big, then a correlated subquery is a simple way to do what you want:

select i.dateFact,
       (select sum(i2.impressions)
        from tblFactImps i2
        where i2.dateFact >= i.dateFact - interval 6 day
       ) as impressions_last_7
from tblFactImps i;

score 1 · Answer 2 · edited May 23 '17 at 11:58

You can achieve this by LEFT OUTER JOINing the table with itself on a date range, and summing the impressions grouped by date, as follows:

SELECT
    t1.dateFact,
    SUM(t2.impressions) AS impressions_last_7
FROM
    tblFactImps t1
LEFT OUTER JOIN
    tblFactImps t2
ON
    t2.dateFact BETWEEN
        DATE_SUB(t1.dateFact, INTERVAL 6 DAY)
        AND t1.dateFact
GROUP BY
    t1.dateFact;

This should give you a sliding 7-day sum for each date in your table.

Assuming your dateFact column is indexed, this query should also be relatively fast.

Summing data for last 7 day look back window

2 Answers2