Top Salaries by Department
Hard
Given the database with the schema shown below, write a SQL query to fetch the top earning employee by department, ordered by department name.
employees projects +---------------+---------+ +---------------+---------+ | id | int |<----+ +->| id | int | | first_name | varchar | | | | title | varchar | | last_name | varchar | | | | start_date | date | | salary | int | | | | end_date | date | | department_id | int |--+ | | | budget | int | +---------------+---------+ | | | +---------------+---------+ | | | departments | | | employees_projects +---------------+---------+ | | | +---------------+---------+ | id | int |<-+ | +--| project_id | int | | name | varchar | +-----| employee_id | int | +---------------+---------+ +---------------+---------+
Your query should return a result in the following format:
department_name | employee_id | first_name | last_name | salary ----------------+-------------+------------+-----------+-------- varchar | int | varchar | varchar | int
Our solution will involve using the window function RANK() to find the highest salary per department and an inner join to return employees with their associated departments. Here's our full solution:
select
t.department_name,
t.employee_id,
t.first_name,
t.last_name,
t.salary
from
(select
d.name as department_name,
e.id as employee_id,
e.first_name,
e.last_name,
e.salary,
rank() over (partition by d.id order by e.salary desc) as rnk
from
employees e
inner join
departments d
on
e.department_id = d.id) t
where
rnk = 1
order by
department_name;
with emp_by_salary as ( select id, first_name, last_name, salary, department_id, rank() over (partition by department_id order by salary desc) as rnk from employees ) select d.name as department_name, e.id as employee_id, e.first_name, e.last_name, e.salary from emp_by_salary e join departments d on e.department_id=d.id where e.rnk=1 order by 1;