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.

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.