Interview Question Bank · DENSE_RANK · PARTITION BY · COALESCE
6. Top two total-pay levels per department
MediumBankingQuestion #266
Concept: DENSE_RANK · PARTITION BY · COALESCEshow
For 'top N per group, ties included', rank inside each group with DENSE_RANK() OVER (PARTITION BY group ORDER BY value DESC) and keep rank <= N. DENSE_RANK gives tied rows the same number and doesn't skip the next one; RANK skips, and ROW_NUMBER breaks ties arbitrarily. If the value is a sum of columns, COALESCE the nullable ones first, because NULL + anything is NULL and NULL sorts first in DESC order.
Example (different data):
SELECT * FROM (
SELECT league, team, wins + COALESCE(bonus_points, 0) AS points,
DENSE_RANK() OVER (PARTITION BY league ORDER BY wins + COALESCE(bonus_points, 0) DESC) AS rnk
FROM standings
) t
WHERE rnk <= 2;Your task
Larkspur Bank's compensation review compares total pay (base salary plus bonus) across departments. People who have left stay in the table with a left_on date, and some current staff have no bonus recorded.
For current staff only (left_on is NULL), total_pay = base_salary + bonus, where a NULL bonus means no bonus. Return everyone holding one of the top 2 distinct total_pay amounts in their department, ties included: department, name, total_pay and pay_rank (1 or 2). Sort by department, pay_rank, then name.
Starting Postgres in your browser…
Checking your access…