Chapter 12 · 13 min · Basic
Rolling windows and smoothing
Daily numbers are unreadable. Weekends halve them, one promotion triples them, and nobody can tell a trend from a Tuesday.
So everyone reaches for a seven-day average, and the frame goes in almost without thinking: rows between 6 preceding and current row.
That frame contains two assumptions. One is that every day is present in the table. The other is that seven is the right number. Both are wrong often enough to change what your chart says, and neither announces itself.
The dataset
Daily order counts across the 28 calendar days from 2 to 29 March 2026 — 27 rows, because one day is absent. Weekdays run near 100 and weekends near 40 — a strong weekly rhythm. Two things are deliberate: 11 March is missing entirely (an outage, no row at all — not a zero), and 18 March is a promotion day at 400 orders.
Schema
| order_date | date |
| orders | int |
Example data
ROWS counts rows. It does not count days.
rows between 6 preceding and current row means the current row and the six rows before it in the ordering. Nothing in it mentions time.
On a complete daily series those are the same thing, which is why this works everywhere until it doesn't. Run it and read the days_spanned column.
From 12 March onward the window holds seven rows spanning eight calendar days, because 11 March has no row to hold. The column header says seven-day average; the window is quietly reaching back further to fill its quota.
This is the same failure as LAG skipping a missing month, one frame clause over. A row-counting frame walks the rows that exist, and missing data does not exist.
It stays wrong for exactly the window length — seven rows — then heals itself, which is why nobody catches it: the chart has one slightly-too-smooth stretch and no error anywhere.
27 rows are seeded across 28 calendar days. How many of them will report a window spanning more than seven days?
Basic
The rest of this chapter is Basic
Learning SQL is free here, forever. This track is the paid half: what to do when the data is dirty, duplicated and undocumented, and somebody still wants a number.
RM 25/mo · cancel anytime
Still to come in this chapter
- 02RANGE counts time — and then divides by the wrong denominator
- 03Three honest answers, and you have to pick one
- 04The window size is an editorial choice
- 05WHERE runs before the window, and it will eat your frame
- 06The start of the series is not smooth, it is short
- 07Now do it where the gaps are not on screen