# Second to One

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

Canonical URL: <https://datadriven.io/problems/second-to-one-department-salaries>

Domain: SQL · Difficulty: medium · Seniority: junior

## 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.

## 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**

```sql
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
```

> **DENSE_RANK over ROW_NUMBER**
>
> ROW_NUMBER would hand the two top earners ranks 1 and 2, so your 'second highest' would just be the top salary again, reported for the wrong person. DENSE_RANK ties them both at 1 and makes the genuine next-lower figure rank 2. Picking the right ranking function is the whole question.

> **Common Pitfall**
>
> The instinct from the single-table version is MAX(salary) WHERE salary < (SELECT MAX(salary) ...). Correlate that subquery per department and it works, but it scans the table once per group and falls apart the moment you also want third or fourth highest. The windowed ranking gives you any Nth level by changing one literal.

> **Cost Analysis**
>
> On a real HR warehouse of a few million employee rows, this is one pass plus a partitioned sort. An index on (department, salary DESC) lets the engine feed the window function pre-sorted, so the DENSE_RANK is effectively free. The DISTINCT only de-dupes the handful of tie rows that survive the rank=2 filter, not the whole table.

> **Interviewers Watch For**
>
> Whether you ask, out loud, what 'second highest' means when salaries tie, and whether departments with only one distinct salary should appear at all. Naming that those single-salary departments simply drop out (no rank 2 exists) signals you reasoned about the data, not just the happy path.

---

## 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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/second-to-one-department-salaries)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.