Find the second-highest (or Nth-highest) salary per department.

TL;DR: Use DENSE_RANK over a per-department window and filter salary_rank = N, which makes "Nth highest" mean Nth distinct salary, the usual business intent. LIMIT/OFFSET works for the global case but breaks the moment the question says "per department."

How to approach it

Clarify two things out loud before writing anything: (1) ties, if two people share the top salary, is the second-highest the next person or the next distinct value? (2) what to return for a department with fewer than N distinct salaries, no row, or NULL? Asking these is most of the signal; the SQL itself is short.

A strong answer

The generalizes-to-any-N solution is DENSE_RANK over a per-department window:

WITH ranked AS (
  SELECT
    department_id,
    employee_id,
    salary,
    DENSE_RANK() OVER (
      PARTITION BY department_id
      ORDER BY salary DESC
    ) AS salary_rank
  FROM employees
)
SELECT department_id, employee_id, salary
FROM ranked
WHERE salary_rank = 2;

Explain the choice: DENSE_RANK makes "2nd-highest" mean "2nd-highest distinct salary," which is almost always the business intent. RANK would skip (1, 1, 3, rank 2 never exists if two people tie for first); ROW_NUMBER would call a duplicate of the top salary "second-highest."

The RANK failure is strange enough that it deserves to be watched rather than described. Take a Sales department where Priya and Marcus both earn 120,000 and Chen earns 95,000. RANK assigns 1, 1, 3, and filtering rnk = 2 returns zero rows (executed against exactly this data): the query does not return a wrong person, it returns nobody, for a department that visibly has a second-highest salary. DENSE_RANK assigns 1, 1, 2 and returns Chen. An empty result for "second-highest salary in Sales" is the kind of output that gets shrugged off as "I guess the data's weird" in a hurry, which is exactly how it survives review; knowing in advance that RANK manufactures holes after ties is what lets you catch it at the whiteboard instead.

On Snowflake or Databricks, mention QUALIFY to skip the CTE:

SELECT department_id, employee_id, salary
FROM employees
QUALIFY DENSE_RANK() OVER (
  PARTITION BY department_id ORDER BY salary DESC
) = 2;

If asked for the value only (not the employees), the old-school alternatives show range: MAX(salary) below the department max via a correlated subquery, or OFFSET 1 LIMIT 1 on distinct salaries, and you can note that the correlated version is O(N²)-ish on naive engines, which is why the window version is preferred at scale.

Edge cases to name unprompted: departments with one employee return nothing at rank 2 (use a LEFT JOIN from a departments table if the report needs every department); NULL salaries sort last under ORDER BY salary DESC in most engines but check NULLS LAST semantics; and on huge tables the partition keeps the sort bounded per department.

The question behind the question is what happens with ties, and asking it before writing is worth more than the query. Then the per-department part: PARTITION BY department and a filter in an outer query, because you cannot filter on a window function in the same WHERE.

What interviewers probe next

• "Now Nth-highest, parameterized", same query, = :n; that's why you led with DENSE_RANK rather than nested MAX.
• "Whole row vs just the value?", the window version returns rows naturally; aggregate versions need a join back.
• "What if I want all three top salaries per department?", pivot to the top-N-per-group pattern, <= 3.
• "How does this perform on 1B rows?", one shuffle/sort per partition key; fine if departments are well distributed, discuss skew if one department has 90% of rows.

Common mistakes

• Jumping straight to LIMIT 1 OFFSET 1, doesn't do "per department" and breaks on ties.
• Using ROW_NUMBER without acknowledging duplicate salaries.
• Forgetting PARTITION BY department_id (global second-highest instead of per-department).
• Not asking about ties at all, at Meta and Microsoft this question exists almost entirely to see whether you ask.