Second Highest Salary

Second Highest Salary

What is this problem about?

The Second Highest Salary interview question asks you to query a salary table and return the second-highest distinct salary. If no second-highest salary exists (because there is only one distinct salary or the table is empty), return NULL. This is a classic SQL ranking and null-handling problem that tests multiple approaches: subquery, LIMIT/OFFSET, and window functions.

Why is this asked in interviews?

Apple, Microsoft, Meta, Amazon, Google, Bloomberg, and Adobe ask this SQL problem because ranking and "Nth highest" queries are among the most common in business analytics. Returning NULL when the result doesn't exist (rather than an empty result set) is a nuance that separates careful SQL writers from beginners. This problem directly tests knowledge of DISTINCT, ORDER BY, LIMIT, OFFSET, and the IFNULL/COALESCE pattern.

Algorithmic pattern used

Multiple approaches work. Subquery approach: select the maximum salary that is less than the overall maximum:

SELECT IFNULL(
    (SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT 1 OFFSET 1),
    NULL
) AS SecondHighestSalary;

Window function approach: use DENSE_RANK() to rank salaries, then filter for rank = 2:

SELECT MAX(salary) AS SecondHighestSalary
FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM Employee) t
WHERE rnk = 2;

Example explanation

Employee salaries: [100, 200, 300].

• MAX salary = 300.
• Second highest: MAX salary where salary < 300 = 200. Return 200.

Salaries: [100, 100].

• Only one distinct salary. No second-highest → return NULL.

Common mistakes candidates make

• Using MAX without DISTINCT, accidentally returning the same salary when there are duplicates.
• Not wrapping the subquery in IFNULL — if no second salary exists, the inner query returns an empty set; without IFNULL, the column shows nothing instead of NULL.
• Using RANK() instead of DENSE_RANK() — RANK() skips numbers on ties, so the second unique salary might have rank 3, not 2.
• Using LIMIT 1, 1 (MySQL shorthand) without knowing it's not ANSI SQL.

Interview preparation tip

For the Second Highest Salary coding problem, the database window function and subquery interview pattern is essential. Know all three approaches: subquery with LIMIT/OFFSET, nested MAX with exclusion, and DENSE_RANK() window function. Interviewers at Visa and Barclays often ask for the generalized "Nth highest salary" — parameterize your DENSE_RANK solution with a variable N. Practice the IFNULL/COALESCE null-handling pattern, which appears in every "return NULL if not exists" SQL problem.