Nth Highest Salary Per Department

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: L5

Problem

For each department, find the employee with the third-highest salary. If a department has fewer than 3 employees, exclude it. Return department, employee name, and their salary.

Third place in every department.

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 20 rows
department	emp_name	salary
Data	Xan Sullivan	127369
Data	Xan Sullivan	127369
Design	Wes Barnes	118363
Design	Wes Barnes	118363
Engineering	Oak Russell	116521

Worked solution and explanation

Why this problem exists in real interviews

Against the employees table, dense ranking on emp_name values is the key operation. Interviewers favor this in mid-level screens because it exposes whether candidates handle ties, NULLs, and ordering edge cases correctly.

Break down the requirements

Step 1: Rank employees within each department

DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) assigns rank 1 to the highest salary in each department, with ties sharing a rank.

Step 2: Filter to rank 3

Wrap in a subquery and WHERE rnk = 3 to get the third-highest salary per department.

Step 3: Exclude small departments

Departments with fewer than 3 employees will have no row at rank 3, so they naturally drop out. Alternatively, add a HAVING COUNT(*) >= 3 guard.

The solution

Window rank then filter to position 3

SELECT department, emp_name, salary
FROM (
    SELECT
        department,
        emp_name,
        salary,
        DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 3

Common follow-up questions

What happens to your result if `employees.emp_name` contains NULLs for some rows? (Tests whether the candidate accounts for NULL behavior in aggregates and comparisons on `emp_name`.)
If two rows in employees have identical values in the ORDER BY columns, how does your ranking handle the tie? (Tests understanding of RANK vs `DENSE_RANK` vs `ROW_NUMBER` tie-breaking behavior.)
What index would you add to employees to avoid a full table scan when filtering or sorting by `employee_id`? (Tests practical indexing decisions for numeric filter columns.)