Running totals and moving averages: frames

Part of the free LogicWiz SQL & Databases course, Module 11: Window functions. The queries and practice run in the app on real Postgres.

The Orange Cap graphic shows each batter's season total after every match. Meher builds it in one line, then uses LAST_VALUE to find each match's top scorer, and every row says the top scorer is the player on that row. Same cause, both times: the window frame.

You'll learn to

• Turn SUM into a running total by adding ORDER BY to OVER
• Write moving averages with ROWS BETWEEN
• Explain the default RANGE frame and how ties change a running total
• Avoid the LAST_VALUE trap

Learn

A running total: just add ORDER BY

SELECT m.match_date, b.runs,
       SUM(b.runs) OVER (ORDER BY m.match_date) AS season_runs
FROM batting b
JOIN matches m ON m.match_id = b.match_id
WHERE b.player_id = 7
ORDER BY m.match_date;

Without ORDER BY, SUM OVER () is the full total on every row. With ORDER BY, each row sums from the first row up to itself: 81, 183, 192, 251.

The frame: which rows the window adds up

Inside a partition, the frame is the slice of rows an aggregate actually uses for the current row. You can set it:

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

means "this row and the two before it": a 3-row moving window. Other edges: UNBOUNDED PRECEDING (start of the partition), n FOLLOWING, UNBOUNDED FOLLOWING (end of the partition).

Frames matter for SUM, AVG, MAX, FIRST_VALUE and LAST_VALUE. ROW_NUMBER, RANK, LAG and LEAD ignore them.

A 3-match moving average

SELECT m.match_date, SUM(b.runs) AS total_runs,
       ROUND(AVG(SUM(b.runs)) OVER (ORDER BY m.match_date
             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS avg_last_3
FROM matches m
JOIN batting b ON b.match_id = m.match_id
GROUP BY m.match_id, m.match_date
ORDER BY m.match_date;

The first row averages one match, the second two, then every row averages the last three. Moving averages smooth noisy numbers: daily orders, weekly UPI volume, a batter's form.

The default frame is RANGE, not ROWS

If you write ORDER BY and no frame, Postgres uses:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

RANGE treats rows with the same ORDER BY value (peers) as one step. So when there are ties, every tied row gets the total including all its peers, not a total that grows one row at a time. With a unique ORDER BY, RANGE and ROWS give the same answer.

Ties: RANGE vs ROWS

SELECT name, price_cr,
       SUM(price_cr) OVER (ORDER BY price_cr DESC) AS range_total,
       SUM(price_cr) OVER (ORDER BY price_cr DESC, name
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_total
FROM players
ORDER BY price_cr DESC, name
LIMIT 8;

All four ₹18 crore players show a range_total of 93.00 (21 + 4 x 18) because they are peers. rows_total adds one player at a time: 39, 57, 75, 93.

The LAST_VALUE trap. LAST_VALUE(name) OVER (PARTITION BY match_id ORDER BY runs) uses the default frame, which ends at the current row. So the "last value" is the current row itself (or its last peer). Give it the whole partition: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or use FIRST_VALUE with the order reversed.

LAST_VALUE, wrong and right

SELECT p.name, b.runs,
       LAST_VALUE(p.name) OVER (ORDER BY b.runs) AS wrong_top,
       LAST_VALUE(p.name) OVER (ORDER BY b.runs
                                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS top_scorer
FROM batting b
JOIN players p ON p.player_id = b.player_id
WHERE b.match_id = 1
ORDER BY b.runs;

Frame cheat sheet

Interviewers love: "running total of sales per day", "7-day moving average of orders", "cumulative percentage of revenue", and "what is the default frame?". Say it out loud: with ORDER BY, the default frame is RANGE from the start of the partition to the current row and its peers.

In one line

SUM(x) OVER (ORDER BY d) is a running total; the default frame is RANGE up to the current row and its peers, so use ROWS for one-row steps and a full frame for LAST_VALUE.

Interview corner

How do you compute a running total?

SUM(amount) OVER (PARTITION BY account ORDER BY txn_at, txn_id). ORDER BY makes it cumulative; PARTITION BY restarts it per account. Add a unique tie-breaker (or a ROWS frame) so rows with the same timestamp do not jump together.

What is the difference between ROWS and RANGE frames?

ROWS counts physical rows: "2 PRECEDING" is exactly two rows back. RANGE works on the ORDER BY value: all rows with the same value (peers) are in or out together. The default with ORDER BY is RANGE UNBOUNDED PRECEDING to CURRENT ROW, which is why ties share one running total.

Why does LAST_VALUE return the current row?

Because the default frame ends at the current row. Use LAST_VALUE(x) OVER (PARTITION BY g ORDER BY y ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), or simply FIRST_VALUE(x) OVER (PARTITION BY g ORDER BY y DESC).

Write a 7-day moving average of daily orders.

Aggregate to one row per day, then AVG(orders) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). If some days have no orders, first build a full calendar (generate_series) and LEFT JOIN, or the "7 rows" will span more than 7 days.