Postgres Practice 10/12

Window functions: RANK with PARTITION BY

GROUP BY answers "what's the total per region?" - but it collapses the rows to answer. The moment the question becomes "how does each individual sale rank within its region?", GROUP BY can't help: you need every row to survive, each annotated with information about its group.

That's a window function. It looks over a "window" of related rows while leaving the rows themselves intact:

RANK() OVER (PARTITION BY region ORDER BY amount DESC)

Read the OVER clause inside-out: PARTITION BY region splits the rows into per-region windows (like GROUP BY, but without collapsing), and ORDER BY amount DESC says what "rank 1" means inside each window - highest amount first. Each region gets its own independent 1, 2, 3, ... - the numbering restarts at every partition boundary.

(ROW_NUMBER() is the sibling that never ties; RANK() gives equal values equal rank and skips ahead after a tie. With no ties in this data they agree - knowing both names helps when reading other people's queries.)

Same sales table: rep, region, month, amount.

Your task: show every sale's rep, region, amount, and its rank within its region by amount (highest = 1), as a column named rank_in_region. Order by region, then rank.

You'll practice:

Writing RANK() OVER (PARTITION BY ... ORDER BY ...)
Keeping row-level detail while adding group-level standing

-- Rank each sale within its region (highest amount = rank 1).
-- GROUP BY would collapse the rows - a window function keeps them.
SELECT rep, region, amount
FROM sales
ORDER BY region, amount DESC;