Finding nth Highest Salary within each department

Rank within each department, then filter to N. PARTITION BY restarts the numbering per department, so one query answers it for every department at once.

SELECT department, employee_id, salary
FROM (
  SELECT employee_id,
         department,
         salary,
         DENSE_RANK() OVER (PARTITION BY department
                            ORDER BY salary DESC) AS rk
  FROM   employees
) ranked
WHERE rk = :n;

Parameterise it safely

The natural follow-up is "now make N configurable". Pass it as a bind parameter, as above, rather than concatenating it into the SQL string. String-building a query from user input is a SQL injection hole, and it also defeats plan caching because every value of N produces a textually different statement. A bind parameter reuses one cached plan.

Choosing the ranking function

DENSE_RANK treats tied salaries as one rank and skips nothing, so N means "the Nth distinct salary" — usually the intended reading. RANK skips ranks after a tie, so rank N may not exist and the query can return nothing. ROW_NUMBER never ties but picks arbitrarily between equal salaries, so results are not reproducible unless you add a deterministic tiebreaker such as ORDER BY salary DESC, employee_id.

Why not a correlated subquery

The classic pre-window approach counts distinct higher salaries per row. It is correlated, so it re-scans the department for every row and degrades quadratically. The window version sorts once per partition and streams through. On any realistic table the difference is large, and knowing why is the point of the question.

Performance and edge cases

A composite index on (department, salary DESC) supplies rows in partition and sort order, letting the engine skip the sort. Departments with fewer than N distinct salaries return no row, which is correct but worth stating. If callers need every department represented, LEFT JOIN the ranked set back onto the department list.

In the interview, also mention that TOP N per group is the same pattern with rk <= :n.