Second Highest Salary Per Department

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

Medium

Description

You are given a table of staff members. Each staff member has a name, a department, and a salary. Write a SQL query to find the second highest distinct salary in each department. If a department does not have at least two distinct salary values, it should not appear in the result. Return the result ordered by department. Table: Staff

Column Name	Type	Description
id	INT	Primary key
name	VARCHAR	Staff member name
dept	VARCHAR	Department name
salary	INT	Annual salary

Database Schema (Inferred)

Staff

Column Name	Example Value
id	1
name	Alice
dept	Eng
salary	100000

Example

Staff

id	name	dept	salary
1	Alice	Eng	100000
2	Bob	Eng	90000
3	Carol	Eng	80000
4	Dave	Sales	70000
5	Eve	Sales	70000
6	Frank	Sales	60000

Output

dept	second_highest
Eng	90000
Sales	60000

Explanation:

In Eng, the max salary is 100000 so the second highest is 90000. In Sales, both Dave and Eve earn 70000 (the max), so the second distinct salary is 60000.

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.