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

最后

以上就是激情硬币最近收集整理的关于LeetCode 615 Average Salary: Departments VS Company的全部内容，更多相关LeetCode内容请搜索靠谱客的其他文章。