Write a query returning the second-highest salary in each department, then tell me what it does when two people tie for the top.

Short answer

Use DENSE_RANK() over partition by department order by salary desc, then keep rank = 2. That returns the second distinct salary per department and handles tied top salaries correctly.

Clarify the requirement before writing SQL

"Second-highest salary" is ambiguous, and the interviewer usually knows it. With salaries of 90,000, 90,000 and 75,000 in one department, the second-highest distinct salary is 75,000, but the second-highest-paid employee earns 90,000. Asking which one is wanted takes ten seconds and is graded. Candidates who guess have a fifty percent chance of solving a different problem well.

Assume for the rest of this answer that the requirement is the second-highest distinct salary, and that ties at the top should collapse to one rank.

The version most candidates write first

-- Wrong. The subquery is uncorrelated, so it is the company-wide maximum.
SELECT   department_id, MAX(salary) AS second_highest
FROM     employees
WHERE    salary < (SELECT MAX(salary) FROM employees)
GROUP BY department_id;

This reads convincingly and returns a plausible-looking result set, which is what makes it dangerous. The subquery excludes only the single highest earner in the whole company, so for every department except the one containing that person, the query returns that department's highest salary. One row is right and the rest are wrong, and a spot check on the first row of output will not catch it.

Correlating the subquery fixes the arithmetic:

SELECT   e.department_id, MAX(e.salary) AS second_highest
FROM     employees e
WHERE    e.salary < (SELECT MAX(x.salary)
                     FROM   employees x
                     WHERE  x.department_id = e.department_id)
GROUP BY e.department_id;

This is correct for distinct salaries, and it handles ties at the top properly because the strict < removes every row holding the maximum, not just one of them. It has one behaviour worth naming out loud: a department where everybody earns the same amount contributes no surviving rows, so it vanishes from the output rather than appearing with a NULL. Whether that is a bug depends on the report, but you should be the one who says it, not the interviewer.

The window-function answer

SELECT department_id, employee_id, salary
FROM (
  SELECT e.department_id,
         e.employee_id,
         e.salary,
         DENSE_RANK() OVER (PARTITION BY e.department_id
                            ORDER BY e.salary DESC) AS salary_rank
  FROM   employees e
  WHERE  e.salary IS NOT NULL          -- see below: DESC sorts NULLs first here
) ranked
WHERE salary_rank = 2;

The three ranking functions are not interchangeable, and the difference only shows up on tied data. On 90,000 / 90,000 / 75,000:

• ROW_NUMBER produces 1, 2, 3, so = 2 returns the second person on 90,000 — the top salary again, dressed up as an answer.
• RANK produces 1, 1, 3, so = 2 matches nothing and the department silently disappears.
• DENSE_RANK produces 1, 1, 2, which is the distinct-value semantics asked for.

ROW_NUMBER is the right choice only when you genuinely want the second-highest-paid person and are content for the tie to be broken arbitrarily. If you use it, add a deterministic tiebreaker such as ORDER BY salary DESC, employee_id so the query returns the same row on every run rather than depending on plan choice.

The detail that separates the two grades

Nullable salaries. In PostgreSQL, ORDER BY salary DESC defaults to NULLS FIRST, so an employee with an unrecorded salary takes rank 1 in their department and the genuine top earner is reported as the second-highest. Oracle behaves the same way for descending sorts. The fix is either the WHERE salary IS NOT NULL filter above or ORDER BY salary DESC NULLS LAST, and knowing which of the two you want is a real decision: filtering drops the employee entirely, while NULLS LAST keeps them in the partition at the bottom where they cannot distort the ranks you care about. Almost nobody raises this unprompted, and raising it moves an adequate answer to a strong one.

The sequence-gap variant

The same partition-and-compare shape answers the other question in this family. Given invoice numbers with holes, LAG gives each row its predecessor and any jump larger than one is a gap:

SELECT prev_number + 1 AS gap_start,
       invoice_number - 1 AS gap_end
FROM (
  SELECT invoice_number,
         LAG(invoice_number) OVER (ORDER BY invoice_number) AS prev_number
  FROM   invoices
) t
WHERE invoice_number - prev_number > 1;

The first row has a NULL prev_number, and because NULL > 1 is unknown rather than true, that row is excluded by the WHERE clause without any special handling. Being able to explain why it is excluded, rather than noticing it happens to work, is the point.

---

© 2026 Preptima. Originally published at preptima.com.