PatternSQL
7-row moving average
Smooth a noisy daily metric into a trailing 7-row average using AVG() OVER with an explicit ROWS BETWEEN frame.
Last updated
Pattern
SELECT
day,
amount,
AVG(amount) OVER (
ORDER BY day
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg_7
FROM daily_sales
ORDER BY day;Why it’s written this way
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW defines a window of exactly 7 physical rows — the current one plus the six immediately before it in the ORDER BY sequence — and AVG() runs over just that slice for every row, sliding forward one row at a time as it moves down the table.
This is a frame over rows, not over calendar time: it counts input rows, so it only equals a true 7-calendar-day average when daily_sales has exactly one row per day with no gaps in between.
Edge cases to know
- →A missing day (no sales recorded) shifts the row-based window without any warning — it ends up averaging 7 rows that span more than 7 calendar days, rather than skipping the gap, which quietly understates how far back the window is really reaching.
- →The first six rows of the table don't have six rows before them yet; ROWS BETWEEN still computes an average over whatever is available rather than returning NULL, so the earliest values in the output are partial averages, not full 7-row ones.
- →Swapping ROWS for RANGE changes the meaning: RANGE groups by the value of day itself (every row within 6 units of the current one's value), which behaves differently the moment there are duplicate or irregularly spaced dates.