Second Highest Salary Per Department

The Problem

A company is doing succession planning. For each department, they want to identify who would be next in line from a compensation perspective if the highest-paid person left. This is also a common ask during restructuring -- if you cut the most expensive headcount per team, who remains at the top?

The wrinkle: salaries can tie. If two people share the top salary, the "second highest" should be the next distinct salary value, not simply the second row.

The Pattern: Window Functions with DENSE_RANK

This question tests your understanding of ranking functions and how they handle ties. SQL offers three ranking variants:

ROW_NUMBER() -- assigns unique sequential numbers, breaks ties arbitrarily
RANK() -- ties get the same number, but the next rank skips (1, 1, 3)
DENSE_RANK() -- ties get the same number, no gaps (1, 1, 2)

Choosing the wrong one here gives wrong results. If two people tie for first, RANK() would assign rank 1 to both and rank 3 to the next person -- so filtering WHERE rnk = 2 returns nothing.

This pattern appears wherever you need the "Nth item per group":

Top 3 products by revenue per category
Second-fastest lap time per driver in a race
Finding runner-up bidders in auctions
Identifying backup suppliers -- the next cheapest vendor per part
Leaderboard positioning with tied scores

The PARTITION BY + ORDER BY + filter pattern is one of the most versatile tools in analytical SQL.

The Setup

CREATE TABLE dept_employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2)
);

INSERT INTO dept_employees (id, name, department, salary) VALUES
(1, 'Alice', 'Engineering', 120000),
(2, 'Bob', 'Engineering', 110000),
(3, 'Charlie', 'Engineering', 110000),
(4, 'Diana', 'Sales', 90000),
(5, 'Eve', 'Sales', 85000),
(6, 'Frank', 'Sales', 80000),
(7, 'Grace', 'Marketing', 95000),
(8, 'Hank', 'Marketing', 88000);

Note that Bob and Charlie tie at 110000 in Engineering. Your solution should handle this correctly.

The Challenge

Find the employee(s) with the second highest salary in each department. If two people share the highest salary, the next distinct salary is considered "second highest."

Try your solution here

Discuss on Discord