Second Highest Salary per Department (DENSE_RANK)

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

You are given a table of employees. Each employee belongs to a department and has a salary. Write a SQL query to find the employee(s) with the second-highest salary in each department. Use DENSE_RANK so that ties at rank 1 do not skip rank 2. If a department has fewer than two distinct salary levels, it should not appear in the result. Return department, employee name, and salary ordered by department. Table: Staff

Column Name	Type	Description
id	INT	Primary key
name	VARCHAR	Employee name
department	VARCHAR	Department name
salary	INT	Annual salary

Database Schema (Inferred)

Staff

Column Name	Example Value
id	1
name	Alice
department	Eng
salary	95000

Example

Staff

id	name	department	salary
1	Alice	Eng	95000
2	Bob	Eng	90000
3	Carol	Eng	85000
4	Dave	Sales	70000
5	Eve	Sales	70000
6	Frank	Sales	60000
7	Grace	HR	55000

Output

department	name	salary
Eng	Bob	90000
Sales	Frank	60000

Explanation:

In Eng, Bob has the 2nd highest salary. In Sales, Eve and Dave tie for 1st (DENSE_RANK=1), so Frank at 60000 is rank 2. HR only has one employee - excluded.

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.