Top two salaries per department

Hardsqlwindow functionsdense rankjoin

For each department, return the employees whose salary is among the department's two highest distinct salaries: department, employee and salary. Sort by department, then salary from highest, then employee name.

Tables

departments (id INTEGER, name TEXT)
employees (id INTEGER, name TEXT, salary INTEGER, department_id INTEGER)

Example 1

departments

id	name
1	Engineering
2	Sales

employees

id	name	salary	department_id
1	Mona	30000	1
2	Omar	25000	1
3	Laila	30000	1
4	Ziad	20000	1
5	Hana	15000	2

Expected result

department	employee	salary
Engineering	Laila	30000
Engineering	Mona	30000
Engineering	Omar	25000
Sales	Hana	15000

Constraints

0 ≤ rows ≤ 1000
The order of rows matters

Submit checks your solution against 6 tests, including hidden ones.

SQL

-- Write one SELECT query. Tables: departments, employees
SELECT