SQL - How to sum revenue by customer over the last 7 days for each date-CodePudding

I want to sum the previous 7 days revenue from each date for each customer. There are some missing dates for some customers and various different customers so I cannot use a Lag function. I was previously using windows but I could only partition by customer_ID and could not partition by the date range as well.

Some sample data as follows:

Customer_ID	Date	Revenue
1	01/02/21	$20
2	01/02/21	$30
1	02/02/21	$40
2	02/02/21	$50
1	03/02/21	$20
2	03/02/21	$60
1	04/02/21	$10
2	04/02/21	$80
1	05/02/21	$100
2	05/02/21	$40
1	06/02/21	$20
2	06/02/21	$30
1	07/02/21	$50
2	07/02/21	$70
1	08/02/21	$10
2	08/02/21	$20
1	09/02/21	$3
2	09/02/21	$40

This result would give the sum of the previous seven days revenue broken down by customer ID for each date. It is ordered by Customer_ID and Date

Customer_ID	Date	Revenue
1	01/02/21	$20
1	02/02/21	$60
1	03/02/21	$80
1	04/02/21	$90
1	05/02/21	$190
1	06/02/21	$210
1	07/02/21	$260
1	08/02/21	$250
1	09/02/21	$240
2	01/02/21	$30
2	02/02/21	$80
2	03/02/21	$140
2	04/02/21	$220
2	05/02/21	$260
2	06/02/21	$290
2	07/02/21	$360
2	08/02/21	$350
2	09/02/21	$340

Data: Database table

Query Result: Query Result

CodePudding user response：

select customer_id,date,sum(revenue) from customer_table where date >= sysdate-7 and date < =sysdate group by customer_id,date;

Hope this helps you

CodePudding user response：

You can try going with a self join, where you match on:

tab1.customer_id = table2.customer_id
tab1.date being matched with till-6-days-before records of tab2.date.

Then apply the SUM on t2.revenues and aggregate on the selected fields.

SELECT t1.Customer_ID, 
       t1.Date,
       SUM(t2.Revenue) AS total
FROM      tab t1 
LEFT JOIN tab t2
       ON t1.Customer_ID = t2.Customer_ID
      AND t1.Date BETWEEN t2.Date AND DATEADD(day, -6, t2.Date)
GROUP BY t1.Customer_ID, 
         t1.Date

This approach would avoid the issue of missing dates for customers, as long as you are comparing dates instead of taking the "last 7 records" with LAG.