7-Day Rolling Sales by Region

SQL coding challenge · Difficulty: medium · +150 XP

Problem

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

Tables

Table: regional_sales

| region | sale_date | sales |
| --- | --- | --- |
| East | 2024-01-01 | 100 |
| East | 2024-01-02 | 200 |
| East | 2024-01-03 | 150 |
| East | 2024-01-04 | 120 |
| East | 2024-01-05 | 180 |
| East | 2024-01-06 | 90 |
| East | 2024-01-07 | 210 |
| East | 2024-01-08 | 60 |
| West | 2024-01-01 | 300 |
| West | 2024-01-02 | 100 |
| West | 2024-01-03 | 250 |

Expected Output

| region | sale_date | sales | rolling_7day_sum |
| --- | --- | --- | --- |
| East | 2024-01-01 | 100 | 100 |
| East | 2024-01-02 | 200 | 300 |
| East | 2024-01-03 | 150 | 450 |
| East | 2024-01-04 | 120 | 570 |
| East | 2024-01-05 | 180 | 750 |
| East | 2024-01-06 | 90 | 840 |
| East | 2024-01-07 | 210 | 1050 |
| East | 2024-01-08 | 60 | 1010 |
| West | 2024-01-01 | 300 | 300 |
| West | 2024-01-02 | 100 | 400 |
| West | 2024-01-03 | 250 | 650 |

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