OVER and PARTITION BY
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 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?
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.
GROUP BY trainer_idOVER ()OVER (PARTITION BY trainer_id)WITH … AS (SELECT …, AVG(level) OVER () …)Exercise 7 — Plantachu's partition
With COUNT(*) OVER (PARTITION BY trainer_id), what does Plantachu (trainer_id NULL) get?
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.
-- Write your query below