OVER and PARTITION BY

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

▶ Do the exercises

Exercise 1 — A column that sees the others

With what you learnt, comparing each Codémon with the average level takes a subquery — a GROUP BY alone would not do, since it collapses the rows into one per group. A window function does it in one line: it keeps every row and adds a column computed from the other rows. Start with the average itself:

SELECT name, level, AVG(level) OVER () AS avg_all FROM codemon ORDER BY codemon_id;

AVG(level) OVER () is the usual AVG, followed by OVER (). The parentheses after OVER describe the window — the set of rows the function looks at — and empty parentheses mean all the rows of the result. Nothing is grouped: 8 Codémons in, 8 rows out, each one carrying the average, 11.5. And since that average now sits on every row, the comparison is a plain subtraction:

SELECT name, level, level - AVG(level) OVER () AS vs_average FROM codemon ORDER BY vs_average DESC;

Aquachu is 3.5 levels above the average, Plantachu 3.5 below — one query, no subquery, every row kept. Here the window was the whole table. The next step is to give each trainer a window of their own.

Window vs GROUP BY

GROUP BY answers "one number per group" and loses the rows. A window function answers "each row, plus a number computed from its neighbours" — and keeps them all. Every aggregate you know (COUNT, SUM, AVG, MIN, MAX) can be followed by OVER.

Exercise 2 — PARTITION BY: one window per team

Comparing with the whole league is a start, but a Codémon is usually judged against its own team. PARTITION BY splits the window into groups — one window per trainer:

SELECT name, trainer_id, level, ROUND(AVG(level) OVER (PARTITION BY trainer_id), 1) AS team_avg FROM codemon ORDER BY trainer_id, level DESC;

Each Codémon now sees the average of its own team: 12.5 for Alex's two, 12.5 for Morgan's two, 11.3 for Taylor's three. Plantachu, the wild one, has a NULL trainer_id — NULL forms a partition of its own, so its average is simply its own level.

You are not limited to one window column, nor to AVG. Several window columns can share a SELECT, each with its own window — here the team size, the team's best level and the league's best level, side by side:

SELECT name, trainer_id, level, COUNT(*) OVER (PARTITION BY trainer_id) AS team_size, MAX(level) OVER (PARTITION BY trainer_id) AS team_best, MAX(level) OVER () AS league_best FROM codemon ORDER BY trainer_id, level DESC;

team_size counts the rows of the partition, team_best takes its maximum, and league_best has no PARTITION BY, so it looks at the whole table. Same rows, three different windows, still eight lines.

When the window runs

Window functions run after WHERE, GROUP BY and HAVING, on the rows that survived. Filter first, window second: a WHERE clause changes what the window can see.

Exercise 3 — Rows in, rows out

SELECT name, SUM(level) OVER () AS total FROM codemon; — how many rows come back?

Options
• 8 — one per Codémon, each with the total
• 1 — the total alone
• 4 — one per trainer
• None: SUM needs a GROUP BY

Hint : A window function never groups rows.

Exercise 4 — Share of the team

Complete the query so that pct_of_team is each Codémon's level as a percentage of the total levels of its trainer's team.

Hint : The aggregate that adds up, and the keyword that splits the window by trainer.

Exercise 5 — Where a window can live

So far every window sat in the SELECT list, and that is where it belongs (the final ORDER BY accepts one too). What about WHERE? Try to keep only the Codémons above the average:

-- Error: windows are computed after WHERE SELECT name, level FROM codemon WHERE level > AVG(level) OVER ();

It fails: WHERE filters the rows before the windows are computed, so at that moment the average does not exist yet. The fix is a tool you already have — compute the window in a CTE, then filter the CTE:

WITH with_avg AS ( SELECT name, level, AVG(level) OVER () AS avg_all FROM codemon ) SELECT name, level, avg_all FROM with_avg WHERE level > avg_all ORDER BY level DESC;

Four Codémons sit above the league average of 11.5. This CTE, then filter pattern comes back constantly with window functions — keep it in mind. One last piece of syntax before moving on: when several columns share the same window, name it once with a WINDOW clause instead of repeating it:

SELECT name, level, ROUND(AVG(level) OVER w, 1) AS team_avg, MAX(level) OVER w AS team_best FROM codemon WINDOW w AS (PARTITION BY trainer_id) ORDER BY codemon_id;

WINDOW w AS (…) sits after FROM (and WHERE) and before ORDER BY; each OVER w reuses it. Same result as writing the window twice, less to read.

Exercise 6 — Who does what

Match each piece of syntax with what it does.

Items to match
• GROUP BY trainer_id
• OVER ()
• OVER (PARTITION BY trainer_id)
• WITH … AS (SELECT …, AVG(level) OVER () …)
Definitions
• One row per trainer; the Codémon rows disappear
• One window holding every row of the result
• One window per trainer; every row is kept
• The way to filter on a window value

Exercise 7 — Plantachu's partition

With COUNT(*) OVER (PARTITION BY trainer_id), what does Plantachu (trainer_id NULL) get?

Options
• 1 — NULL forms a partition of its own
• NULL — nothing can be computed
• 8 — it is counted with everyone
• 0 — it has no trainer

Hint : For PARTITION BY, NULL rows are grouped together, like in GROUP BY.

Exercise 8 — Above or below the team

Complete the query so that position says 'above' when a Codémon's level is strictly greater than the average of its trainer's team.

Hint : Strictly greater, and the keyword that turns AVG into a window function.

Exercise 9 — Above the team average

Professor Pi wants the Codémons that pull their team up.

Return trainer_id, name, level and team_avg (the average level of the trainer's team, rounded to 1 decimal) for the Codémons whose level is strictly above their team's average. Ignore wild Codémons. A window cannot be filtered in WHERE, so compute it in a CTE first. Sort by trainer_id, then level descending.

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