Window frames & moving averages

Smooth noisy numbers

Lesson 11 of 20 · 11 min · Module 3: Window functions in depth

Single orders jump up and down, which hides the trend. A moving average over the last few orders smooths the line, and window frames let you choose exactly which rows it uses.

Start this lesson →

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

Define a window frame with ROWS BETWEEN
Calculate moving averages
Understand frames at the start of the data

The idea

A frame such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW limits the window to the current row and the two before it. AVG(total) OVER (ORDER BY … ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) is a three-order moving average.

SELECT id, price_cents,
       MAX(price_cents) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS local_max
FROM products;

01Frames choose the rows

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means "this row and the two before it". FOLLOWING looks ahead, and UNBOUNDED PRECEDING starts at the first row.

02The start has fewer rows

The first order has no earlier orders, so its average is just its own total. The second averages two orders. Only from the third row on are there three values.

03ROWS versus RANGE

ROWS counts physical rows. RANGE groups rows with equal ORDER BY values, which is the default for ordered windows and explains why ties behave differently.

SELECT id, COUNT(*) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM products LIMIT 3;

Output

1 | 1
2 | 2
3 | 3

Your turn

For each paid or shipped order in date order (then id), return id, total_cents, and moving_avg_cents: the average of this order and up to two earlier orders, rounded to a whole number of cents. Sort by date, then id.

What the checks look for

Averages each order with up to two earlier ones

Try it in the editor →

Take this with you

Frames let a window look at exactly the rows you choose.

Go a little further

Show the difference between each order total and its moving average to spot unusual orders.