Ranking and top-N

Part of the free LogicWiz SQL & Databases course, Interview pack: SQL interview problems. The queries and practice run in the app on real Postgres.

"Top 3 earners in each department." Easy, until two engineers earn exactly the same. Is that 3 people or 4? The interviewer is waiting for you to ask.

You'll learn to

• Pick ROW_NUMBER, RANK or DENSE_RANK from the wording of the question
• Write top-N per group with a window in a subquery
• Compute percent of total and cumulative share without the integer-division trap
• Make running totals behave with ties

Learn

Three ranks, one tie

"Exactly one per group" → ROW_NUMBER with a tie-breaker. "Top 3 people, ties included, then a gap" (a sports table) → RANK. "Top 3 salary levels" → DENSE_RANK.

All three side by side

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC, name) AS row_num,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense
FROM employees
WHERE dept_id = 10
ORDER BY salary DESC, name;

The top-N per group pattern

SELECT department, name, salary
FROM (
  SELECT d.name AS department, e.name, e.salary,
         DENSE_RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS r
  FROM employees AS e
  JOIN departments AS d ON d.dept_id = e.dept_id
) AS ranked
WHERE r <= 2
ORDER BY department, salary DESC, name;

Rank inside a subquery (or CTE), filter outside. PARTITION BY restarts the numbering for every department.

You cannot write WHERE DENSE_RANK() OVER (…) <= 3. Window functions run after WHERE, so Postgres says "window functions are not allowed in WHERE". Rank in a subquery, filter outside it.

For running totals, SUM(x) OVER (ORDER BY y) uses a RANGE frame by default: rows with the same y are peers and all get the same total. Add a tie-breaker to the ORDER BY, or say ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

In one line

One per group: ROW_NUMBER with tie-breakers; ties share a place: RANK (gaps) or DENSE_RANK (levels); rank in a subquery, filter outside.

Interview corner

How do you get the top 3 salaries in each department?

Rank inside each department with a window, then filter outside: SELECT * FROM (SELECT e.*, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS r FROM employees e) t WHERE r <= 3;. Ask first whether ties count; that decides DENSE_RANK, RANK or ROW_NUMBER.

How do you compute each row's percent of the total?

Divide by a window total: 100.0 * amount / SUM(amount) OVER (), or per group with OVER (PARTITION BY …). Use 100.0 so the division is not integer division, and ROUND at the end.

Why might a running total give two rows the same value?

With ORDER BY in OVER and no frame, Postgres uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and rows with equal ORDER BY values are peers that share one total. Add a unique tie-breaker or use a ROWS frame.