# Nth Highest Salary Per Department

> Third place in every department.

Canonical URL: <https://datadriven.io/problems/nth_highest_salary_per_department>

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.

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

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

> **Cost Analysis**
>
> With `employees` (80,000 rows), this query scans a small dataset. No indexing is needed for this volume. At production scale, an index on the primary filter column would improve performance.

> **Interviewers Watch For**
>
> Interviewers evaluate whether you translate the English requirements into the correct SQL clauses on the first attempt. They watch for clean syntax, correct column references, and whether you verify edge cases before declaring the query complete.

> **Common Pitfall**
>
> The most common mistake is misreading the prompt's filtering or grouping requirements. Double-check which columns to group by, which to aggregate, and whether the output should be filtered with `WHERE` (before grouping) or `HAVING` (after grouping).

---

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

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/nth_highest_salary_per_department)
- [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.