Chapter 4 · 9 min
Ranking and top-N per group
Ranking functions assign a position to each row inside a partition. They differ in how they treat ties, and that difference is usually the point of the interview question.
row_number()returns 1, 2, 3, … with no repeats, even when rows tie.rank()returns the same number for tied rows and then skips the following positions, so three tied rows are 1, 1, 1, 4.dense_rank()returns the same number for tied rows without skipping, so three tied rows are 1, 1, 1, 2.
Choosing between them is a requirements decision, not a style preference. If the prompt says
“exactly three products per category”, row_number() with a deterministic tiebreaker is the answer.
If it says “every product whose price is in the top three prices”, dense_rank() keeps tied
products that share third place.
WITH ranked AS (
SELECT
p.category,
p.product_id,
p.name,
sum(oi.quantity * oi.unit_price) AS revenue,
row_number() OVER (
PARTITION BY p.category
ORDER BY sum(oi.quantity * oi.unit_price) DESC, p.product_id ASC
) AS rn
FROM products AS p
JOIN order_items AS oi ON oi.product_id = p.product_id
GROUP BY p.category, p.product_id, p.name
)
SELECT category, product_id, name, round(revenue, 2) AS revenue
FROM ranked
WHERE rn <= 3
ORDER BY category ASC, rn ASC;
Filtering on a window result
Postgres does not allow a window function in the WHERE clause, because WHERE runs before windows
are computed. The fix is to compute the rank in a CTE or subquery and filter in the outer query, as
above. HAVING is not a substitute either; it filters groups, not rows.
Tiebreakers
The ORDER BY inside OVER must be unique if you need a stable top-N. Ranking by revenue alone
leaves ties to the engine’s discretion, which can change which product appears in the result. Add
the primary key as a final ordering column and repeat the same ordering in the outer ORDER BY.
Top-N by group versus global top-N
A global top-N is ORDER BY ... LIMIT n. A per-group top-N needs a partition and a rank, because
LIMIT applies to the whole result. Interview prompts often blur the two; read for “per category”,
“per customer”, or “for each country” before choosing the shape.