MediumSQL
3-Day Moving Average
SQLDatabasesWindow Functions
Table: `daily_sales`
| day | amount |
|---|---|
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
| 4 | 40 |
| 5 | 50 |
Write a query that returns each day's amount alongside a **moving
average** over that day and the two preceding days (or fewer, near the
start).
Expected output (columns: day, amount, moving_avg), ordered byday:
1|10|10.0
2|20|15.0
3|30|20.0
4|40|30.0
5|50|40.0Hint — unlike the running-total window functions elsewhere in this
catalog (
OVER (ORDER BY ...)with an implicit unbounded frame), a movingaverage needs an explicit, bounded frame: `ROWS BETWEEN 2 PRECEDING
AND CURRENT ROW`.
Sample tests
Test #1Five days, steadily increasing sales
Input: "CREATE TABLE daily_sales (day INTEGER, amount INTEGER);\nINSERT INTO daily_sales VALUES\n (1, 10), (2, 20), (3, 30), (4, 40), (5, 50);"
Output: "1|10|10.0\n2|20|15.0\n3|30|20.0\n4|40|30.0\n5|50|40.0"
Test #2Single day — average of itself
Input: "CREATE TABLE daily_sales (day INTEGER, amount INTEGER);\nINSERT INTO daily_sales VALUES (1, 100);"
Output: "1|100|100.0"