Running totals and frames
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Running totals
You have seen ORDER BY inside a window decide a numbering. Put it inside an aggregate's window and something new happens: the aggregate stops summarising the whole window, and each row gets the total so far. Badges earned in the league, in date order:
SELECT b.earned_on, t.name,
COUNT(*) OVER (ORDER BY b.earned_on) AS badges_so_far
FROM badges b
INNER JOIN trainers t ON t.trainer_id = b.trainer_id
ORDER BY b.earned_on;Row by row, badges_so_far climbs from 1 to 10 — ORDER BY inside the window means "accumulate in this order".
Partition it and every trainer gets a personal counter:
SELECT t.name, b.earned_on,
COUNT(*) OVER (PARTITION BY b.trainer_id ORDER BY b.earned_on) AS badges_so_far
FROM badges b
INNER JOIN trainers t ON t.trainer_id = b.trainer_id
ORDER BY t.name, b.earned_on;Alex counts to 3, Morgan to 5, Taylor to 2. The same works with SUM — the cumulative strength of the league, weakest Codémon first:
SELECT name, level, SUM(level) OVER (ORDER BY level) AS running_total
FROM codemon
ORDER BY level;Running = ORDER BY in the window
Without ORDER BY, SUM(level) OVER () is the grand total on every row: 92. With ORDER BY level, it becomes a running total that ends at 92.
Exercise 2 — The frame
The running total hides a rule worth knowing. A window with an ORDER BY does not look at all its rows: it looks at a frame, by default *from the first row up to the current one*. That frame can be written explicitly — and changed. The badges so far, and the badges still to come:
SELECT earned_on,
COUNT(*) OVER (ORDER BY earned_on
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS so_far,
COUNT(*) OVER (ORDER BY earned_on
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS remaining
FROM badges;ROWS BETWEEN … AND … names the first and last row of the frame, relative to the current row: UNBOUNDED PRECEDING is the first row of the partition, UNBOUNDED FOLLOWING the last. so_far counts up to 10 while remaining counts down to 1.
A frame of fixed size gives a moving calculation — here the total level of each Codémon and the two before it:
SELECT codemon_id, name, level,
SUM(level) OVER (ORDER BY codemon_id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS last_three
FROM codemon;15 alone, then 15 + 10, then 15 + 10 + 14 = 39, and from there always three rows at a time.
And the LAST_VALUE trap from the previous chapter? Solved by widening the frame to the whole partition:
SELECT trainer_id, name, level,
LAST_VALUE(name) OVER (PARTITION BY trainer_id ORDER BY level DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS weakest
FROM codemon
ORDER BY trainer_id, level DESC;ROWS or RANGE
The exact default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With RANGE, rows tied on the ORDER BY value enter the frame together, so a running total can jump by several rows at once. ROWS counts physical rows, one at a time. When the ORDER BY has ties, write ROWS explicitly or add a tiebreaker.
Exercise 3 — With or without ORDER BY
SUM(level) OVER (ORDER BY level) evaluated on Aquachu, the strongest Codémon (level 15), returns:
Hint : Ascending order puts the strongest last, so its frame covers all eight rows.
Exercise 4 — Personal counter
Complete the query so that badges_so_far counts each trainer's badges cumulatively, in date order, restarting for each trainer.
Hint : One window per trainer, accumulated by date.
Exercise 5 — Frames
Match each frame with what it computes.
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWROWS BETWEEN 2 PRECEDING AND CURRENT ROWROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWINGExercise 6 — Totals on top of groups
Running totals shine on aggregated data. Badges per month, then the cumulative count — GROUP BY first in a CTE, window after:
WITH per_month AS (
SELECT strftime('%Y-%m', earned_on) AS month, COUNT(*) AS badges
FROM badges
GROUP BY month
)
SELECT month, badges, SUM(badges) OVER (ORDER BY month) AS cumulative
FROM per_month;Four badges in January, four more in February, two in March — 10 by the end.
One subtlety remains: the default RANGE frame, which matters as soon as the ORDER BY has ties. Badges given per gym, accumulated in order of that count:
WITH per_gym AS (
SELECT g.name, COUNT(b.badge_id) AS given
FROM gyms g
LEFT JOIN badges b ON b.gym_id = g.gym_id
GROUP BY g.gym_id
)
SELECT name, given,
SUM(given) OVER (ORDER BY given) AS running_total
FROM per_gym
ORDER BY given, name;The three gyms at 2 all jump to 7: with RANGE, rows tied on the ORDER BY value enter the frame together. A complete ORDER BY (count, then name) removes the ties, and the total climbs one gym at a time:
WITH per_gym AS (
SELECT g.name, COUNT(b.badge_id) AS given
FROM gyms g
LEFT JOIN badges b ON b.gym_id = g.gym_id
GROUP BY g.gym_id
)
SELECT name, given,
SUM(given) OVER (ORDER BY given, name) AS running_total
FROM per_gym
ORDER BY given, name;1, 3, 5, 7, 10 — one step per gym. Rule of thumb: when the ORDER BY of a running total can have ties, add a tiebreaker (or write ROWS explicitly).
Exercise 7 — A moving window
SUM(level) OVER (ORDER BY codemon_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) for Voltachu (id 2, level 10), who sits between Aquachu (15) and Pyrochu (14), returns:
Hint : One row before, the row itself, one row after: 15 + 10 + 14.
Exercise 8 — Two counters
Professor Pi follows the badges as they are handed out, gym by gym and league-wide.
For every badge, return earned_on, gym (the gym's name), gym_so_far (how many badges that gym had given up to and including this one) and league_so_far (how many badges had been given in the whole league up to and including this one). Sort by earned_on.
-- Write your query below