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 615 Average Salary: Departments VS Company所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。