Level 18: Window functions: running totals and averages

Lesson

At level 17, window functions ranked the rows. The usual aggregates (SUM, AVG, COUNT, MIN, MAX) also become window functions when you add OVER to them. The difference with GROUP BY: all the rows stay, and each one receives the result of the aggregate computed on its window.

With PARTITION BY alone, the window is the whole group: SUM(amount) OVER (PARTITION BY region) shows on each sale the total of its region, 2600 for the North and 2450 for the South. You can thus compare each row with its group on the same row: gap from the average, share of the total…

OVER () with empty parentheses takes the whole table as the window. It is handy for a share of the total: amount * 100.0 / SUM(amount) OVER () gives the percentage that each sale represents. Write 100.0 to avoid integer division.

With an ORDER BY inside OVER, the calculation becomes cumulative: the window goes from the first row up to the current row. SUM(amount) OVER (PARTITION BY seller ORDER BY month) gives for Ana 300 in January, 750 in February and 1150 in March: it is a running total, like a bank balance. Thanks to PARTITION BY, the running total starts again from zero for each seller.

Watch out for ties: the running total moves forward by sort value, not by row. If two rows have the same value in the ORDER BY, for example two transactions on the same day, they are added together and get the same running total. For a strictly row-by-row running total, add a tie-breaking column (ORDER BY made_on, id). Level 19 explains this mechanism and shows it on an example (exercise 19.4).

Finally, you can combine GROUP BY and a window, since the window is calculated after grouping: SUM(SUM(amount)) OVER () adds up the totals of all the sellers to get the grand total.

Syntax

SELECT col,
       SUM(x) OVER (PARTITION BY g ORDER BY d) AS running,
       x * 100.0 / SUM(x) OVER () AS pct
FROM my_table;

Worked example

SELECT seller,
       month,
       amount,
       SUM(amount) OVER (PARTITION BY seller ORDER BY month) AS running_total
FROM sales;

For each seller, the running total grows month after month and starts again from zero with the next seller.

Example result

Key points

• SUM/AVG/COUNT + OVER: an aggregate on each row, without grouping.
• ORDER BY inside OVER: running total.
• OVER (): the whole table.

Common pitfalls

• Forgetting PARTITION BY gives a running total over the whole table instead of each group.
• Integer division: write 100.0 for a percentage.
• Two rows with the same date get the same running total: that's the default RANGE frame.

The level’s 5 exercises

1. Guided · Show each sale (seller, region, amount) with, on the same row, the total of its region (region_total). (Table: sales)
2. Practice · For account 1, show each transaction (date, amount) and the balance after each operation, in chronological order. (Table: transactions)
3. Practice · Show each grade (student, subject, grade) with its subject's average rounded to one decimal (subject_avg). (Table: grades)
4. Practice · For each city and each day, show the day's rain and the rain accumulated since the start of the month in that city (cum_rain). (Table: readings)
5. Challenge · Show each seller's total and their share of the grand total as a percentage, rounded to one decimal (pct). (Table: sales)

In SpeedQL, every query is checked straight away: its result is compared with the expected one, then the query is run again on a hidden control database. Each exercise has written hints, to show only if you get stuck.

See also: the cheat sheet card · the SQL exercises with solutions on this topic