✏️ Explanatory Question
Level: Coding Round — A LeetCode HARD problem; the "per group" + "ties count as one rank" twist is the challenge.
The Puzzle: Find the employees who are among the top 3 highest earners in each department. A "top 3 salary" means there are no more than two distinct salaries higher than it — so ties at a salary level all count as the same rank.
employees| id | name | salary | dept_id |
|---|---|---|---|
| 1 | Rumman | 90000 | 10 |
| 2 | Krushna | 90000 | 10 |
| 3 | Swetha | 80000 | 10 |
| 4 | Ritesh | 70000 | 10 |
| 5 | Manjula | 60000 | 10 |
| 6 | Elitam | 50000 | 20 |
| dept_id | name | salary |
|---|---|---|
| 10 | Rumman | 90000 |
| 10 | Krushna | 90000 |
| 10 | Swetha | 80000 |
| 10 | Ritesh | 70000 |
| 20 | Elitam | 50000 |
Dept 10 top-3 distinct salaries are 90k, 80k, 70k. BOTH people at 90k qualify, and 70k is still 3rd → 4 employees make the cut. Manjula (60k) is 4th → excluded.
The critical choice — DENSE_RANK, not ROW_NUMBER: "Top 3 salaries" means the top 3 distinct salary values, so tied employees share a rank. DENSE_RANK() gives ties the same rank with no gaps (1,1,2,3...), which is exactly right. ROW_NUMBER would wrongly cut off one of the two 90k earners.
SELECT dept_id, name, salary
FROM (
SELECT dept_id, name, salary,
DENSE_RANK() OVER (
PARTITION BY dept_id
ORDER BY salary DESC
) AS drnk
FROM employees
) t
WHERE drnk <= 3
ORDER BY dept_id, salary DESC;-- An employee is "top 3" if fewer than 3 DISTINCT higher salaries exist in the dept
SELECT e.dept_id, e.name, e.salary
FROM employees e
WHERE (
SELECT COUNT(DISTINCT e2.salary)
FROM employees e2
WHERE e2.dept_id = e.dept_id
AND e2.salary > e.salary
) < 3
ORDER BY e.dept_id, e.salary DESC; Why COUNT(DISTINCT ...) is essential in Solution 2: Without DISTINCT, the two 90k earners would each count the other as a "higher-or-equal" row, corrupting the rank. Counting distinct higher salaries correctly treats tied salaries as one level — mirroring DENSE_RANK's behaviour.
| Requirement | Function |
|---|---|
| Top 3 distinct salary levels (ties included) | DENSE_RANK |
| Exactly 3 rows, break ties arbitrarily | ROW_NUMBER |
| Competition ranking with gaps | RANK |
Interviewer follow-up: "The business now wants exactly 3 employees per department, no more, even when salaries tie." → Switch to ROW_NUMBER() with a deterministic tie-breaker: ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, id) then WHERE rn <= 3. This caps it at exactly 3 by using id to break salary ties — a different business rule requiring a different function.