7-Day Rolling Sum of Positive Sales Only

SQL coding challenge · Difficulty: medium · +200 XP

Problem

Compute a 7-day (7-row) rolling sum of positive sales only — negative (refund) days count as 0.

Tables

Table: daily_sales

| sale_date | sales |
| --- | --- |
| 2024-01-01 | 100 |
| 2024-01-02 | -50 |
| 2024-01-03 | 200 |
| 2024-01-04 | -30 |
| 2024-01-05 | 150 |
| 2024-01-06 | 80 |
| 2024-01-07 | -20 |
| 2024-01-08 | 60 |

Expected Output

| sale_date | sales | rolling_7day_positive |
| --- | --- | --- |
| 2024-01-01 | 100 | 100 |
| 2024-01-02 | -50 | 100 |
| 2024-01-03 | 200 | 300 |
| 2024-01-04 | -30 | 300 |
| 2024-01-05 | 150 | 450 |
| 2024-01-06 | 80 | 530 |
| 2024-01-07 | -20 | 530 |
| 2024-01-08 | 60 | 490 |

• Return: sale_date, sales, rolling_7day_positive
• Sort by sale_date ascending
• Function to use: SUM(CASE WHEN sales > 0 THEN sales ELSE 0 END) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)