Top earners per department
Mediumsqljoinwindow functionssubquery
For each department, return department (its name), employee and salary for the employee or employees earning that department's highest salary. Order doesn't matter.
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 | 20000 | 2 |
| 4 | Ziad | 30000 | 1 |
Expected result
| department | employee | salary |
|---|---|---|
| Engineering | Mona | 30000 |
| Engineering | Ziad | 30000 |
| Sales | Laila | 20000 |
Constraints
- 0 ≤ rows ≤ 1000
- Any row order is accepted
Submit checks your solution against 9 tests, including hidden ones.
SQL
-- Write one SELECT query. Tables: departments, employees SELECT