11. Seven-day moving average
Asked in screens shaped like: Netflix, Spotify, Uber
Daily revenue is noisy, so the dashboard smooths it over a trailing week. The frame has to include the current day and the six before it — off-by-one here is the most common review comment on this query.
Requirements
- Columns: order_day, revenue, avg_7d
- Only status = 'completed'
- avg_7d averages the current day plus the 6 preceding days
- Both numeric columns rounded to 2 decimals, ordered by order_day
Expected output
order_day, revenue, avg_7d
Row order is graded, so ORDER BY matters here.
Tables this problem reads. The full warehouse is available in thesandbox.
orders
~200 rows
Order headers. One row per order, so amount is the order total — not a line total.
- order_idINTEGER— 1000–1199
- customer_idTEXT— C01–C12
- countryTEXT— US, DE, IN, UK, BR
- amountREAL
- statusTEXT— 'completed' or 'cancelled'
- ordered_atTEXT— YYYY-MM-DD HH:MM:SS
Pick a warehouse dialect above the editor. The engine is still SQLite; common syntax is rewritten before it runs.
SQLite cheat sheet
- Truncate to day
DATE(ordered_at) - Current date
DATE('now') - Conditional
CASE WHEN … THEN … ELSE … END - String aggregate
GROUP_CONCAT(col, ", ") - JSON field
json_extract(payload, '$.source') - Date difference
JULIANDAY(a) - JULIANDAY(b) - Filter a window
CTE, then WHERE rn = 1 - Median
ROW_NUMBER + COUNT(*) OVER ()
Revealed one at a time. The reference query stays in the Solution tab.
Hint 1
You need an explicit frame, not the default one.
Hint 2
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW is seven rows total.
Hint 3
ROWS counts rows; RANGE counts values. With one row per day they agree — with gaps they do not.
WITH daily AS (
SELECT DATE(ordered_at) AS order_day, SUM(amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY DATE(ordered_at)
)
SELECT order_day,
ROUND(revenue, 2) AS revenue,
ROUND(AVG(revenue) OVER (ORDER BY order_day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS avg_7d
FROM daily
ORDER BY order_day;Why it works
The first six rows average fewer than seven days because the frame is clipped at the start of the partition. That is expected; forcing NULL there needs a COUNT check in the frame.
Run your query to see the result set.
Submit to run the checks.
Why it works
The first six rows average fewer than seven days because the frame is clipped at the start of the partition. That is expected; forcing NULL there needs a COUNT check in the frame.
Pro problem
Hard problems unlock with Pro
Easy pads stay free. Medium and hard SQL and Python problems — editor, tests, hints, and solutions — open after you upgrade to Pro or coaching.
See plansPractice free easy problems