Moving averages: seven rows is not seven days

Smoothing a noisy line

Daily revenue jumps around. Weekends dip, a promotion spikes, a bank holiday empties the chart. A seven-day moving average replaces each day’s figure with the mean of that day and the six before it, and the weekly rhythm disappears, leaving the trend.

SELECT day, revenue,
       avg(revenue) OVER (ORDER BY day
                          ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM daily_revenue;

The frame is the whole idea: instead of running from the start of the partition, it runs from six rows back to here. ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING is a centred seven-day window, which has no lag against the trend but cannot be computed for the most recent three days. ROWS BETWEEN 29 PRECEDING AND CURRENT ROW is the 30-day version.

The first six rows

On the first day of the series the frame contains one row, on the second two, and so on. avg() divides by the rows it has, so the first six values are averages of shorter windows and are systematically noisier. Either drop them, mark them, or report the count alongside:

count(*) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS days_in_window

and filter WHERE days_in_window = 7 in an outer layer. A chart that starts with a partial window shows a false step on day seven, when the average suddenly has its full complement of rows.

Seven rows is only seven days if every day is there

A ROWS frame counts rows. If the table has no row for a day with no revenue, “6 PRECEDING” reaches back further than six days, and the moving average is over whatever seven trading days happened to exist. The line looks smooth and means something different at each point. There are two fixes.

The first is the spine from the earlier module: generate every date, LEFT JOIN the revenue to it, and coalesce missing days to zero (if zero is what a missing day means) before the window. The second is a RANGE frame measured in time rather than rows, which Postgres supports from version 11 and DuckDB supports:

avg(revenue) OVER (ORDER BY day
                   RANGE BETWEEN interval '6 days' PRECEDING AND CURRENT ROW)

This frame includes every row whose day is within six days of the current one, however many rows that is. It is honest about gaps, and it changes the meaning slightly: a missing day now contributes nothing rather than a zero, so the average is over the days that traded. Decide which meaning you want, because the two charts will differ exactly where the business was closed.

A moving average lags the truth

Averaging the last seven days means the line responds to a change about three and a half days after it happened. A trailing 30-day average responds two weeks late. That is the price of smoothing, and it is why a moving average is a bad early-warning signal and a good after-the-fact description. When you need to react quickly, plot the raw series beside the smoothed one, or use a shorter window and accept the noise.

Moving sums and moving distinct counts

The same frame works for sum(), and “revenue in the trailing 30 days” is one of the most useful columns in any daily table. count(DISTINCT customer_id) over a moving frame is not supported, for the reason the running-totals lesson gave: a sliding distinct count cannot be updated one row at a time. For “customers active in the trailing 30 days” the honest query is a self-join from each day to the orders within its window, or a daily table of first-and-last-active dates.

Weighted and exponential

A simple moving average weights each of the seven days equally. An exponentially weighted average gives recent days more weight and never fully forgets old ones, and it cannot be written as a single window frame, because each value depends on the previous smoothed value. A recursive CTE can do it and is unpleasant; the free path is to pull the daily series into Python and call pandas.Series.ewm(span=7).mean(), or to use LibreOffice Calc with a column formula. Know that the option exists, and reach for SQL’s plain moving average first, because it is the one a reader can check by hand.

The habit

State three things for every moving average: the window length, whether it counts rows or days, and what happens on days with no row. If the chart’s caption cannot say all three, the smoothing has hidden something rather than revealed it.