Second Highest Salary by Department

Step 1: Clarify the requirements

Confirm scope before writing SQL: "second highest" means the second highest distinct salary, not the second row by row number (which would return a duplicate if two employees tie for the highest salary). Confirm the output is per department_id, and that a department with fewer than two distinct salaries should be excluded from the result, not returned with a NULL.

Step 2: Trace an example

Employee table: Alice (90000, dept 1), Bob (85000, dept 1), Carol (95000, dept 2). Department 1 has two distinct salaries (90000 and 85000) — second highest is 85000. Department 2 has only one distinct salary (95000) — it is excluded from the result entirely.

Step 3: Naive approach — correlated subquery

For each department, find the max salary strictly less than that department's overall max:

SELECT e.department_id, MAX(e.salary) AS second_highest_salary
FROM Employee e
WHERE e.salary < (
  SELECT MAX(salary) FROM Employee WHERE department_id = e.department_id
)
GROUP BY e.department_id;

This runs on any SQL engine, including versions without window function support, but the correlated subquery re-scans each department for every row — fine at interview scale, worth flagging as quadratic if the interviewer asks about performance at scale.

Step 4: Window function approach — DENSE_RANK

The scalable version ranks salaries per department and filters to rank 2:

SELECT department_id, salary AS second_highest_salary
FROM (
  SELECT department_id, salary,
         DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
  FROM Employee
) ranked
WHERE rnk = 2;

Use DENSE_RANK, not ROW_NUMBER or RANK. ROW_NUMBER assigns every row a unique number even on ties, so two employees tied for the highest salary would push the true second-highest value to rank 3 and lose it. RANK leaves a gap after ties (1, 1, 3), which also skips rank 2 whenever there is a tie for first. DENSE_RANK is the only one of the three that keeps rank 2 pointing at the correct value regardless of ties.

Step 5: Handle ties and edge cases

Walk through out loud: a department with only one distinct salary should produce no row, not a row with NULL — that's why filtering on rnk = 2 (or the subquery's WHERE e.salary < ...) naturally drops single-salary departments without needing a separate HAVING COUNT check. Also confirm how the source data handles NULL salaries; most interviewers expect you to exclude them explicitly with a WHERE salary IS NOT NULL before ranking.

Step 6: Complexity

Window function version: O(n log n) typical, from the sort behind PARTITION BY ... ORDER BY. Space: O(n) for the ranked result set. The correlated subquery version is O(n squared) in the worst case without an index on (department_id, salary).

Step 7: Follow-ups

Nth highest salary: swap the filter to rnk = N, or parameterize N. For performance at scale, add a composite index on (department_id, salary) so the window function's per-partition ordering doesn't require a full sort.

Related reading

• SQL patterns for interviews — window functions, ranking, and other recurring SQL interview patterns.