Rolling Sum vs Daily Target (Show Difference)

SQL coding challenge · Difficulty: medium · +200 XP

Problem

Show each day's 3-day rolling sum next to a daily target of 200, plus the difference (rolling - target*3).

Tables

Table: daily_sales

| sale_date | sales |
| --- | --- |
| 2024-01-01 | 100 |
| 2024-01-02 | 200 |
| 2024-01-03 | 150 |
| 2024-01-04 | 300 |
| 2024-01-05 | 250 |
| 2024-01-06 | 180 |
| 2024-01-07 | 220 |
| 2024-01-08 | 90 |
| 2024-01-09 | 310 |

Expected Output

| sale_date | sales | rolling_3day_sum | diff_vs_target |
| --- | --- | --- | --- |
| 2024-01-01 | 100 | 100 | -500 |
| 2024-01-02 | 200 | 300 | -300 |
| 2024-01-03 | 150 | 450 | -150 |
| 2024-01-04 | 300 | 650 | 50 |
| 2024-01-05 | 250 | 700 | 100 |
| 2024-01-06 | 180 | 730 | 130 |
| 2024-01-07 | 220 | 650 | 50 |
| 2024-01-08 | 90 | 490 | -110 |
| 2024-01-09 | 310 | 620 | 20 |

• Return: sale_date, sales, rolling_3day_sum, diff_vs_target
• Sort by sale_date ascending
• Function to use: SUM() OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)