Drill 6 of 10 in Banking questions
0

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…