7-Day Moving Average of Revenue

Difficulty: Medium · Topics: moving-average, window-functions, frames · Asked at: Amazon, Meta, Google, Netflix

Problem

daily_revenue has exactly one row per day (no gaps). For each day return revenue, ma_7 (the average of the current day and the 6 previous days, rounded to 2 decimals) and ma_7_full, which is the same value but NULL until 7 days of history exist. Order by day.

Schema and sample data

CREATE TABLE daily_revenue (day TEXT PRIMARY KEY, revenue INTEGER);
INSERT INTO daily_revenue VALUES
('2026-05-01',100),('2026-05-02',120),('2026-05-03',90),('2026-05-04',150),('2026-05-05',130),
('2026-05-06',170),('2026-05-07',110),('2026-05-08',200),('2026-05-09',160),('2026-05-10',140);

Expected output

Hints

Solution

SELECT day, revenue,
       ROUND(AVG(revenue) OVER w, 2) AS ma_7,
       CASE WHEN COUNT(*) OVER w = 7 THEN ROUND(AVG(revenue) OVER w, 2) END AS ma_7_full
FROM daily_revenue
WINDOW w AS (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
ORDER BY day;

Explanation

• ROWS BETWEEN 6 PRECEDING AND CURRENT ROW = the last 7 rows. That equals 7 days only because the data has no gaps (see the next problem for gaps).
• The early rows average fewer than 7 values. Dashboards often show these as misleadingly low or high, which is why ma_7_full exists.
• The named WINDOW w clause avoids repeating the spec (supported by Postgres, SQLite, MySQL 8, BigQuery and Spark 3+; not by Snowflake).

Follow-up questions