✏️ Explanatory Question
Level: Hard — A whiteboard classic; the "per group" and "ties" twists trip up most candidates.
employees| emp_id | name | dept_id | salary |
|---|---|---|---|
| 1 | Rumman | 10 | 90000 |
| 2 | Krushna | 10 | 90000 |
| 3 | Swetha | 10 | 75000 |
| 4 | Ritesh | 10 | 60000 |
| 5 | Manjula | 20 | 80000 |
| 6 | Elitam | 20 | 50000 |
DENSE_RANK or ROW_NUMBER — clarify it with the interviewer!
| salary | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 90000 | 1 | 1 | 1 |
| 90000 | 2 | 1 | 1 |
| 75000 | 3 | 3 | 2 |
| 60000 | 4 | 4 | 3 |
"2nd highest" = the second person → ROW_NUMBER = 2 (Krushna, 90000). "2nd highest distinct salary" → DENSE_RANK = 2 (Swetha, 75000).
-- "Second-highest distinct salary value" per department
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 = 2;
-- Dept 10 -> Swetha 75000 | Dept 20 -> Elitam 50000-- "Second-highest-PAID employee" (each person ranked uniquely)
SELECT dept_id, name, salary
FROM (
SELECT dept_id, name, salary,
ROW_NUMBER() OVER (
PARTITION BY dept_id
ORDER BY salary DESC, emp_id -- tie-break for determinism
) AS rn
FROM employees
) t
WHERE rn = 2;
-- Dept 10 -> Krushna 90000 | Dept 20 -> Elitam 50000-- Correlated subquery approach for "2nd highest distinct salary"
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
) = 1; -- exactly one distinct salary is higher -> it's the 2nd highestWHERE drnk = N. Departments with fewer than N distinct salaries simply return no row — mention you'd LEFT JOIN back to departments if you must show every department even when there's no Nth value.