Running totals and frames

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 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:

Options
• 92 — the total of all eight levels
• 15 — its own level
• 11.5 — the average
• NULL

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.

Items to match
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
• ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
Definitions
• From the first row to this one: a running total
• This row and the two before it: a moving window of 3
• The whole partition, whatever the current row
• From this row to the last: what remains

Exercise 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:

Options
• 39
• 25
• 10
• 92

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.

Starter code
-- Write your query below
Back to the course