Interview Questions/SQL/Second Highest Salary per Department (DENSE_RANK)

Second Highest Salary per Department (DENSE_RANK)

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

Medium

Second Highest Salary per Department (DENSE_RANK)

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

Examples

Example 1

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.

query.sql