Department Highest Salary
Google
Problem Statement
Google HR needs to identify the highest-paid individuals in every department for budget reviews. Note that multiple employees can share the highest salary in a department; in such cases, all of them should be reported.
Write an SQL query to find employees who have the highest salary in each of the departments.
Rules:
- Return the result table with columns Department, Employee, and Salary.
- If there is a tie for the highest salary in a department, include all employees with that salary.
- Results must be sorted by Department name in ascending order.
Tests your understanding of
Basic SQL, Window Functions, Joins, RANK and Grouping
Input Tables
employee
| id(INTEGER) | name(VARCHAR) | salary(INTEGER) | department_id(INTEGER) |
|---|---|---|---|
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
| 6 | Janet | 69000 | 1 |
department
| id(INTEGER) | name(VARCHAR) |
|---|---|
| 1 | IT |
| 2 | Sales |
Expected Output
| Department(VARCHAR) | Employee(VARCHAR) | Salary(INTEGER) |
|---|---|---|
| IT | Jim | 90000 |
| IT | Max | 90000 |
| Sales | Henry | 80000 |
Tags
MediumBasic SQLWindow FunctionsJoinsRANKGrouping
15-20 min
52%