Find the third-highest salary for each department.

Rank salaries within each department using a window function, then filter to the rank you want. The department-level grouping comes from PARTITION BY, which restarts the ranking for every department.

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 = 3;

The part interviewers are actually testing

Which ranking function you pick changes the answer, and this is the real point of the question:

DENSE_RANK is the right default because "third highest salary" almost always means the third distinct amount. Say that assumption out loud rather than guessing.

Edge cases

Departments with fewer than three distinct salaries return no row at all, which is usually correct but occasionally surprises stakeholders expecting one row per department. If they want every department represented, LEFT JOIN the ranked set back onto a distinct department list.

A filter cannot reference a window function directly, which is why the ranking sits in a subquery or CTE. WHERE rk = 3 in the same SELECT is a syntax error, because WHERE is evaluated before window functions.

Performance

A composite index on (department, salary DESC) lets the engine feed the window function pre-sorted and skip the sort entirely, which matters once the table is large.

In the interview, also mention that swapping the literal 3 for a bind parameter generalises this to Nth-highest without changing the plan.