7-Day Rolling Sum by Store and Product

SQL coding challenge · Difficulty: hard · +200 XP

Problem

For each row show a 7-day (7-row) rolling sum of sales per store and product.

Tables

Table: store_product_sales

| store_id | product_id | sale_date | sales |
| --- | --- | --- | --- |
| 1 | 1 | 2024-01-01 | 10 |
| 1 | 1 | 2024-01-02 | 20 |
| 1 | 1 | 2024-01-03 | 30 |
| 1 | 1 | 2024-01-04 | 40 |
| 1 | 1 | 2024-01-05 | 50 |
| 1 | 1 | 2024-01-06 | 60 |
| 1 | 1 | 2024-01-07 | 70 |
| 1 | 1 | 2024-01-08 | 80 |
| 1 | 2 | 2024-01-01 | 100 |
| 1 | 2 | 2024-01-02 | 200 |
| 1 | 2 | 2024-01-03 | 300 |

Expected Output

| store_id | product_id | sale_date | sales | rolling_7day_sum |
| --- | --- | --- | --- | --- |
| 1 | 1 | 2024-01-01 | 10 | 10 |
| 1 | 1 | 2024-01-02 | 20 | 30 |
| 1 | 1 | 2024-01-03 | 30 | 60 |
| 1 | 1 | 2024-01-04 | 40 | 100 |
| 1 | 1 | 2024-01-05 | 50 | 150 |
| 1 | 1 | 2024-01-06 | 60 | 210 |
| 1 | 1 | 2024-01-07 | 70 | 280 |
| 1 | 1 | 2024-01-08 | 80 | 350 |
| 1 | 2 | 2024-01-01 | 100 | 100 |
| 1 | 2 | 2024-01-02 | 200 | 300 |
| 1 | 2 | 2024-01-03 | 300 | 600 |

• Return: store_id, product_id, sale_date, sales, rolling_7day_sum
• Sort by store_id, product_id, sale_date
• Function to use: SUM() OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)