7-Day Rolling Average Sales

SQL coding challenge · Difficulty: easy · +100 XP

Problem

The dashboard shows a 7-day rolling average of sales to reveal the weekly trend.

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_7day_avg |
| --- | --- | --- |
| 2024-01-01 | 100 | 100.0000 |
| 2024-01-02 | 200 | 150.0000 |
| 2024-01-03 | 150 | 150.0000 |
| 2024-01-04 | 300 | 187.5000 |
| 2024-01-05 | 250 | 200.0000 |
| 2024-01-06 | 180 | 196.6667 |
| 2024-01-07 | 220 | 200.0000 |
| 2024-01-08 | 90 | 198.5714 |
| 2024-01-09 | 310 | 214.2857 |

• Return: sale_date, sales, rolling_7day_avg
• Sort by sale_date ascending
• Function to use: AVG() OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)