Second highest salary per department

← Problems13. Second highest salary per departmentEasySQL

00:00 / 20:00

Second highest salary per department

Easy24% solve itAsked at Microsoft, Deloitte, Google

Return the second highest salary in each department. If a department has fewer than two distinct salaries, return null for that department. Ties on salary count once.

Input schema

employees employee_id bigint department_id int salary numeric

Constraints

Output one row per department present in employees, ordered by department_id
Duplicate salaries in a department are one distinct value
Up to 2,000,000 rows

Example 1basic two departments

Input · employees

salary	employee_id	department_id
90000	1	10
70000	2	10
50000	3	20
40000	4	20

Output

department_id	second_salary
10	70000
20	40000

Example 2duplicate salaries count once

Input · employees

salary	employee_id	department_id
90000	1	10
90000	2	10
70000	3	10

Output

department_id	second_salary
10	70000

Topics

window functionsaggregation

Second highest salary per department

Input schema

Constraints

Example 1basic two departments

Example 2duplicate salaries count once

Topics

Similar problems