Second to One

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

Domain: SQL
Difficulty: medium
Seniority: junior
Asked in: Cisco

Problem

A compensation review is comparing pay bands across departments. For each department, find the second highest salary, counting everyone who shares a pay figure as a single level, and list the departments alphabetically.

Across every team, the pay figure that sits just shy of the summit.

Tables

employees

Columns: employee_id INTEGER, emp_name TEXT, department TEXT, salary REAL, 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 5 of 10 rows
department	second_highest_salary
Data	130439
Design	127983
Engineering	119591
Finance	128597
HR	135556

Worked solution and explanation

Why this problem exists in real interviews

This is the classic 'second highest salary' question wearing a per-group costume. The single-table version (one MAX below the overall MAX) is a warm-up everyone passes. The real skill being probed is whether you can do that same logic independently inside every department without letting one department's pay bleed into another's, and whether you handle ties: if two people earn the top salary, the 'second highest' is still the next distinct figure down, not the same number again. Conflate those and your pay bands are quietly wrong.

Break down the requirements

Step 1: Rank distinct salaries within each department

PARTITION BY department restarts the ranking per team, and ORDER BY salary DESC puts the top earners first. Use DENSE_RANK, not ROW_NUMBER: DENSE_RANK gives equal salaries the same rank, so the second distinct pay level is always salary_rank = 2 even when several people tie for the top.

Step 2: Keep the second level

Filter to salary_rank = 2. Because DENSE_RANK numbers distinct salary values, this is precisely 'the second highest pay figure in the department', which is what a comp review means by a pay band.

Step 3: Collapse the ties and sort

If multiple employees share that second salary, the rank filter returns one row each, so SELECT DISTINCT department, salary folds them into a single band per department. ORDER BY department gives the alphabetical listing the reviewers asked for.

The solution

Second highest salary per department

WITH salary_ranks AS (
  SELECT department,
         salary,
         DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
  FROM employees
)
SELECT DISTINCT department,
       salary AS second_highest_salary
FROM salary_ranks
WHERE salary_rank = 2
ORDER BY department

Common follow-up questions

Return the Nth highest salary per department where N is a parameter. (Tests whether they see that the window form generalizes by swapping the rank literal, while the MAX-below-MAX form does not.)
A department has only one distinct salary. Should it appear in the output, and how would you make it appear with a NULL? (Probes edge handling and a LEFT JOIN back to the department list to surface missing bands.)
Now return the employees who earn that second-highest salary, not just the figure. (Forces them to keep `emp_name` through the CTE and reason about ties producing multiple names per band.)