LeetCode 615 Average Salary: Departments VS Company

Given two tables as below, write a query to display the comparison result (higher/lower/same) of the average salary of employees in a department to the company's average salary.

 

So for the sample data above, the result is:

| pay_month | department_id | comparison  |
|-----------|---------------|-------------|
| 2017-03   | 1             | higher      |
| 2017-03   | 2             | lower       |
| 2017-02   | 1             | same        |
| 2017-02   | 2             | same        |

 

drop table salary
drop table salary

Create table salary(id int,employee_id int,amount int,pay_date date);
Create table employee(employee_id int,department_id int);
 
insert into salary values(1,2,9000,'2017-03-31');
insert into salary values(2,2,60000,'2016-03-31');
insert into salary values(3,3,10000,'2017-03-31');
insert into salary values(4,1,7000,'2017-02-28');
insert into salary values(5,2,6000,'2017-02-28');
insert into salary values(6,3,8000,'2017-02-28');

insert into employee values(1,1);
insert into employee values(2,2);
insert into employee values(3,2);

select * from salary
select * from employee

Answer:

with cte as(
select CONCAT(YEAR(pay_date),'-',MONTH(pay_date)) As [Month],avg(amount) as company_average
from salary
group by CONCAT(YEAR(pay_date),'-',MONTH(pay_date))
),cte2 as(
select CONCAT(YEAR(pay_date),'-',MONTH(pay_date)) As [Month],
e.department_id,
avg(amount) as department_average
from salary s 
join employee e on s.employee_id=e.employee_id
group by CONCAT(YEAR(pay_date),'-',MONTH(pay_date)),e.department_id
) select c1.[Month] as pay_month,c2.department_id
,case 
when c2.department_average<c1.company_average then 'higher'
when c2.department_average>c1.company_average then 'lower'
else 'same'
end as comparison 
from cte c1 join cte2 c2 
on c1.[Month]=c2.[Month]
order by pay_month desc,c2.department_id asc