How would you calculate a 7-day moving average of daily sales, and what frame clause is needed?

How to think about it

This question is really about frame clauses — not whether you know AVG() OVER (ORDER BY ...) exists. The frame is the subtle part: it controls which rows feed the average, and getting it wrong by one row turns a “7-day” average into an 8-day one.

A window function computes over a frame of rows, and two frame modes matter here:

• ROWS counts physical rows around the current one, ignoring the actual ORDER BY values.
• RANGE counts rows whose ORDER BY value falls within a span of the current row’s value.

For a 7-day moving average, ROWS BETWEEN 6 PRECEDING AND CURRENT ROW means “the 6 rows before this one, plus this one — 7 total.” Note 6 PRECEDING, not 7: six before plus the current row is seven.

A worked example — watch the window fill

Ten days of sales, with a spike on day 5. Alongside the average, COUNT(*) over the same frame exposes the “ramp-up” — the window holds fewer than 7 rows until day 7:

SELECT sale_date, daily_revenue,
       ROUND(AVG(daily_revenue) OVER (
         ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ), 0) AS ma_7d,
       COUNT(*) OVER (
         ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS window_size
FROM daily_sales
ORDER BY sale_date;

sale_date	daily_revenue	ma_7d	window_size
2024-01-01	1000	1000.0	1
2024-01-02	1200	1100.0	2
2024-01-03	900	1033.0	3
2024-01-04	1100	1050.0	4
2024-01-05	3500	1540.0	5
2024-01-06	1050	1458.0	6
2024-01-07	980	1390.0	7
2024-01-08	1150	1411.0	7
2024-01-09	1020	1386.0	7
2024-01-10	1300	1443.0	7

Two things to read off this. First, window_size climbs 1 → 7 over the opening week and then stays at 7 — that’s the frame holding exactly seven rows once enough history exists. Second, the day-5 spike of 3500 lifts the average to 1540 but doesn’t dominate it the way a raw reading would; by day 10 it has slid out of the window entirely. (ROUND(..., 0) returns a float in SQLite, hence the trailing .0.)

Why ROWS, not RANGE

RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW includes every row whose date is within the last 6 calendar days. If the data has gaps — no sales on weekends — those missing dates shrink the denominator unpredictably, so a “7-day” average might quietly average 5 rows. ROWS is deterministic: always exactly N physical rows. For a per-product average, add PARTITION BY product_id and the window resets at each product boundary, so one product’s rows never bleed into another’s.