- A company's executives are interested in seeing
who earns the most money in each of the company's departments. A high earner in
a department is an employee who has a salary in the top three unique salaries
for that department.
Write an SQL query to find the employees who are
high earners in each of the departments.
Return the result table in any order.
table name: Employee
table name: Department
Solution:
with cte as
(
select d.name as Department,e.name as Employee,e.Salary,dense_rank() over (partition by e.departmentId order by salary desc) as rnk
from Employee as e
join Department as d on e.departmentId = d.id
)
select Department,Employee,Salary from cte where rnk in (1,2,3)
Output:
SQL Script:
Create table Employee (id int, name varchar(255), salary int, departmentId int)
Create table Department (id int, name varchar(255))
insert into Employee (id, name, salary, departmentId) values ('1', 'Joe', '85000', '1')
insert into Employee (id, name, salary, departmentId) values ('2', 'Henry', '80000', '2')
insert into Employee (id, name, salary, departmentId) values ('3', 'Sam', '60000', '2')
insert into Employee (id, name, salary, departmentId) values ('4', 'Max', '90000', '1')
insert into Employee (id, name, salary, departmentId) values ('5', 'Janet', '69000', '1')
insert into Employee (id, name, salary, departmentId) values ('6', 'Randy', '85000', '1')
insert into Employee (id, name, salary, departmentId) values ('7', 'Will', '70000', '1')
insert into Department (id, name) values ('1', 'IT')
insert into Department (id, name) values ('2', 'Sales')