Second Highest Salary II - Practice Coding Problems

Second Highest Salary II - Problem

Database Medium

Given a table employees with employee information including their ID, salary, and department, write a SQL query to find the employees who earn the second-highest salary in each department.

Key Requirements:

Find the second-highest salary within each department
If multiple employees have the same second-highest salary, include all of them
If a department has fewer than 2 distinct salary levels, exclude it from results
Return results ordered by emp_id in ascending order

Table Schema

employees

Column Name	Type	Description
`emp_id` PK	int	Unique employee identifier (primary key)
`salary`	int	Employee's salary amount
`dept`	varchar	Department name where employee works

Primary Key: emp_id

Note: Each row represents one employee with their salary and department information

Input & Output

Example 1 — Multiple Departments with Ties

Input Table:

emp_id	salary	dept
1	70000	Sales
2	80000	Sales
3	80000	Sales
4	90000	Sales
5	55000	IT
6	65000	IT
7	65000	IT
8	50000	Marketing
9	55000	Marketing
10	55000	HR

Output:

emp_id	dept
2	Sales
3	Sales
5	IT
8	Marketing

💡 Note:

Sales: Highest is 90000 (emp_id 4), second-highest is 80000 (both emp_id 2 and 3 included due to tie)

IT: Highest is 65000 (emp_id 6,7), second-highest is 55000 (emp_id 5)

Marketing: Highest is 55000 (emp_id 9), second-highest is 50000 (emp_id 8)

HR: Only one salary level (55000), so no second-highest exists - excluded from results

Example 2 — Department with Insufficient Salary Levels

Input Table:

emp_id	salary	dept
1	100000	Engineering
2	100000	Engineering
3	100000	Engineering
4	75000	Finance
5	80000	Finance

Output:

emp_id	dept
4	Finance

💡 Note:

Engineering: All employees have the same salary (100000), so there's only one distinct salary level. No second-highest exists - excluded from results.

Finance: Highest is 80000 (emp_id 5), second-highest is 75000 (emp_id 4).

Example 3 — Empty Result

Input Table:

emp_id	salary	dept
1	90000	Sales
2	60000	IT

Output:

emp_id	dept

💡 Note:

Each department has only one employee, so neither department has a second-highest salary. The result is empty.

Constraints

1 ≤ emp_id ≤ 10^4
1 ≤ salary ≤ 10^6
dept is a non-empty string
Each emp_id is unique

Visualization

Tap to expand

Understanding the Visualization

Input

Employee table with salary and department

Rank

DENSE_RANK salaries within each department

Filter

Select employees with rank = 2

Key Takeaway

🎯 Key Insight: DENSE_RANK is perfect for finding exact nth highest values while handling ties correctly

Asked in

A Amazon 23 G Google 18 M Microsoft 15 F Facebook 12

Use DENSE_RANK() window function with PARTITION BY dept ORDER BY salary DESC to rank salaries within departments, then filter for rank = 2 to get all employees with second-highest salaries.

Table Schema

employees

Column Name	Type	Description
`emp_id` PK	int	Unique employee identifier (primary key)
`salary`	int	Employee's salary amount
`dept`	varchar	Department name where employee works

Primary Key: emp_id

Note: Each row represents one employee with their salary and department information

Common Approaches

Approach	Time	Space	Notes
✓ Window Function with DENSE_RANK	O(n log n)	O(n)	Use DENSE_RANK to identify second-highest salary within each department
Correlated Subquery Approach	O(n²)	O(1)	Use correlated subquery to count distinct higher salaries within each department

Window Function with DENSE_RANK — Algorithm Steps

Step 1: Use DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) to rank salaries within each department
Step 2: Filter results where rank = 2 to get only second-highest salaries
Step 3: Order final results by emp_id ascending

Visualization

Tap to expand

Step-by-Step Walkthrough

Partition

Group employees by department

Rank

Rank salaries within each department

Filter

Select only rank = 2 employees

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting required for ranking within partitions

⚡ Linearithmic

Space Complexity

O(n)

Window function intermediate results

⚡ Linearithmic Space

23.4K Views

High Frequency

~12 min Avg. Time

847 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

Window Function with DENSE_RANK — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler