LeetCode 615. Average Salary: Departments VS Company

drop table salary

Create table salary(Id int,employee_id int,amount int,pay_date date);

insert into salary values(1,1,9000,'2017-03-31');
insert into salary values(2,2,6000,'2017-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');

drop table employee
Create table employee(Id int,department_id int);

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

Given two tables as above, 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
|

with cte1 as(
select FORMAT(pay_date, 'yyyy-MM') as Pay_Month,avg(amount) as comp_pay from salary
group by FORMAT(pay_date, 'yyyy-MM')
),cte2 as(
select e.department_id,FORMAT(pay_date, 'yyyy-MM') as Pay_Month,AVG(s.amount) as dept_pay from salary s join employee e on s.employee_id=e.Id
group by e.department_id,FORMAT(pay_date, 'yyyy-MM')
)
select
cte1.Pay_Month,
cte2.department_id,
case when cte2.dept_pay > cte1.comp_pay then 'higher'
when cte2.dept_pay < cte1.comp_pay then 'lower'
else 'Same' end as comparison
from cte1 join cte2 on cte1.Pay_Month=cte2.Pay_Month
order by Pay_Month desc,cte2.department_id

最后

以上就是玩命手套为你收集整理的LeetCode 615. Average Salary: Departments VS Company的全部内容，希望文章能够帮你解决LeetCode 615. Average Salary: Departments VS Company所遇到的程序开发问题。

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