Window Functions · Episode 18

Moving averages

moving averageframe clause

The story

Leadership looks at last lesson’s daily revenue chart and isn’t thrilled: it jumps all over the place, day to day. They don’t want the running total either — that only ever goes up, so it can’t show whether things are trending up or down recently. They want a smoothed line.

Harry

This revenue line is noisy. Can we smooth it out?

Harry

Could I just swap SUM for AVG in the running total query? AVG(revenue) OVER (ORDER BY placed_at)?

Hermione

Try it. See what it actually gives you.

Harry's attempt: a running average

query.sql

WITH daily AS (
  SELECT placed_at, SUM(amount) AS revenue
  FROM orders
  WHERE status = 'completed'
  GROUP BY placed_at
)
SELECT placed_at, revenue,
  ROUND(AVG(revenue) OVER (ORDER BY placed_at), 2) AS running_avg
FROM daily
ORDER BY placed_at;

This runs fine, and it does smooth things a little — but it’s not what leadership meant. With no frame clause, the window is still “every row from the start through here,” exactly like last lesson’s running total, just averaged instead of summed. By day fifteen, that average is dragging along every single day since December — a genuinely quiet day barely moves it anymore. It never forgets anything. A moving average is supposed to forget the old stuff and only track what’s recent.

The concept: frame clauses

Every window function has a frame: the exact set of rows, relative to the current one, that it looks at. So far we’ve only used the default frame. You can specify it yourself with ROWS BETWEEN ... AND ..., right inside OVER, after the ORDER BY:

Which rows are actually in the window, right now?

query.sql

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

That says: “this row, plus the two rows immediately before it — and nothing else.” As the current row moves forward one day at a time, the whole frame slides forward with it, always three rows wide. Average revenue over that frame, and you get a genuine 3-day moving average.

Build the query

query.sql

WITH daily AS (
  SELECT placed_at, SUM(amount) AS revenue
  FROM orders
  WHERE status = 'completed'
  GROUP BY placed_at
)
SELECT placed_at, revenue,
  ROUND(AVG(revenue) OVER (
    ORDER BY placed_at
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ), 2) AS moving_avg_3
FROM daily
ORDER BY placed_at;

Trace the edges by hand. The very first day has no rows before it, so its 3-row frame can only ever hold itself — its moving_avg_3 is just its own revenue, 199.99. SQL doesn’t error out or pad with zeros when the frame runs off the start of the data; it just uses however many rows actually exist. By the third day, the frame finally has all three rows it wants — 199.99, 34.50, and 79.99 — averaging out to 104.83.

Run it

query.sql⌘/Ctrl + Enter to run

Running…

Notice this is a moving average over the last three order-days that exist in the data, not the last three calendar days. There’s a gap of almost two months between 2023-05-22 and 2023-07-04 with zero orders in between — the frame just skips straight over it and picks up the next three rows that actually have data. A moving average smooths across whatever rows you feed it; it has no idea a calendar exists unless you build that in yourself.

Try it: Try widening the frame to ROWS BETWEEN 4 PRECEDING AND CURRENT ROW — a 5-day moving average instead of 3.

experiment.sql⌘/Ctrl + Enter to run

Run your query to see results here.

A wider frame means each point is an average of more data, so the line reacts more slowly to any single noisy day — smoother, but slower to reflect a genuine recent swing. A narrower frame reacts faster but stays noisier. There’s no universally correct window size; it’s a real trade-off between smoothness and responsiveness, and it depends on what the reader is trying to see.

Your turn

NOVA wants a smoothed view of order value per customer: for every order, show customer_id, placed_at, amount, and a 2-order moving average of amount (the current order and the one right before it, for that same customer) as moving_avg_2. Order the result by customer_id, then placed_at.

Opens in a new window, full width — come back here once you’re done.

Hint 1

This combines two things you already know: PARTITION BY to keep each customer's orders separate, and a frame clause to control how wide the average is.

Hint 2

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW means: this row and the one right before it — two rows total.

Hint 3

AVG(amount) OVER (PARTITION BY customer_id ORDER BY placed_at ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moving_avg_2

Solution

solution.sql⌘/Ctrl + Enter to run

Running…

Debrief

A frame clause is what turns “aggregate everything so far” into “aggregate just this nearby handful of rows.” And it has a nice side effect from last lesson’s gotcha: ROWS BETWEEN always counts physical rows, one at a time, tie or no tie — it never silently groups equal ORDER BY values together the way the default frame did.

One thing to remember

A frame clause — ROWS BETWEEN n PRECEDING AND CURRENT ROW — tells a window function exactly which nearby rows to include, instead of the whole partition. That’s what turns a running total into a moving average, and unlike the default frame, ROWS always counts individual rows, regardless of ties.

Open Playground

← PreviousRunning totals Next →Revenue