codejump
MediumSQL

3-Day Moving Average

SQLDatabasesWindow Functions

Table: `daily_sales`

dayamount
110
220
330
440
550

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 by
day:

1|10|10.0
2|20|15.0
3|30|20.0
4|40|30.0
5|50|40.0

Hint — unlike the running-total window functions elsewhere in this

catalog (OVER (ORDER BY ...) with an implicit unbounded frame), a moving

average 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"
← All challenges