Second Highest Salary

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain: SQL
Difficulty: easy
Seniority: L3
Asked in: Munich Re

Problem

HR is setting the salary band for a new role and needs the second-highest unique salary on record as a benchmark. If all employees share the same salary, return NULL.

Silver medal. Almost the top, but not quite.

Tables

employees

Columns: employee_id INTEGER, emp_name TEXT, salary REAL, department TEXT, manager_id INTEGER, hire_date TEXT, user_id INTEGER

employees: 10 of 200 rows
employee_id	emp_name	department	salary	manager_id	hire_date	user_id
1	Alice Chen	Engineering	55731	NULL	2021-02-02	100
2	Bob Martinez	Product	61462	NULL	2022-03-03	197
3	Carol Wu	Design	67193	NULL	2023-04-04	294
4	David Kim	Marketing	72924	NULL	2024-05-05	391
5	Eve Johnson	Sales	78655	NULL	2025-06-06	488
6	Frank Patel	HR	84386	NULL	2026-07-07	585
7	Grace Lee	Finance	55117	NULL	2020-08-08	682
8	Hank Brown	Operations	60848	NULL	2021-09-09	779
9	Iris Davis	Data	66579	NULL	2022-10-10	876
10	Jake Wilson	Legal	72310	NULL	2023-11-11	973

Expected output

First 1 of 1 rows
second_highest
145790

Worked solution and explanation

This is a distinct-value selection dressed up as a compensation benchmark. The real question: can you return the second-highest salary value, not the second row, and still hand back exactly one NULL row when no such value exists? Everyone reaches for ORDER BY salary DESC LIMIT 1 OFFSET 1. On this table that is wrong: every salary sits on two rows, so the top figure, 150907, also fills row two, and the benchmark HR receives is the maximum itself.

The trap

Second row: ORDER BY salary DESC LIMIT 1 OFFSET 1 counts rows. Two rows share 150907, so it returns 150907. If every salary were equal it would return that salary, and a DISTINCT variant would return zero rows. Neither is the single NULL row the spec asks for.
Second value: MAX(salary) over the rows strictly below the top keeps every copy of 150907 out at once and returns 145790. If nothing is below the top, the aggregate still emits one row, and that row is NULL.

Building it

Step 1: Find the ceiling once

(SELECT MAX(salary) FROM employees) is a scalar subquery: one number, computed once, that every outer row is compared against. No join, no ranking.

Step 2: Keep only what sits strictly below it

WHERE salary < (...) removes every row that ties the top, however many there are. The strict less-than is what collapses the ties; you never need DISTINCT.

Step 3: Take the maximum of what is left

MAX(salary) over the surviving rows is the second distinct value. With no GROUP BY, an aggregate over zero rows still returns one row holding NULL, so the all-equal case needs no extra code.

Second-highest distinct salary

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees)

Common follow-up questions

Now return the Nth-highest distinct salary for any N. (Moves to `DENSE_RANK()`, which ranks ties together, and checks they still return one NULL row when N exceeds the number of distinct values.)
Return the second-highest salary within each `department`. (Tests scoping the ceiling per group, either with a correlated subquery or a partitioned ranking.)
What changes if `salary` can be NULL? (`MAX` ignores NULLs and `NULL < x` is never true, so the answer is unchanged; the candidate should say why.)