# $615. 平均工资：部门与公司比较
# https://leetcode-cn.com/problems/average-salary-departments-vs-company/

# SQL架构
Create table If Not Exists Salary (id int, employee_id int, amount int, pay_date date);
Create table If Not Exists Employee (employee_id int, department_id int);
Truncate table Salary;
insert into Salary (id, employee_id, amount, pay_date) values ('1', '1', '9000', '2017/03/31');
insert into Salary (id, employee_id, amount, pay_date) values ('2', '2', '6000', '2017/03/31');
insert into Salary (id, employee_id, amount, pay_date) values ('3', '3', '10000', '2017/03/31');
insert into Salary (id, employee_id, amount, pay_date) values ('4', '1', '7000', '2017/02/28');
insert into Salary (id, employee_id, amount, pay_date) values ('5', '2', '6000', '2017/02/28');
insert into Salary (id, employee_id, amount, pay_date) values ('6', '3', '8000', '2017/02/28');
Truncate table Employee;
insert into Employee (employee_id, department_id) values ('1', '1');
insert into Employee (employee_id, department_id) values ('2', '2');
insert into Employee (employee_id, department_id) values ('3', '2');

# Write your MySQL query statement below
select
    department_salary.pay_month,
    department_id,
    case
        when department_avg > company_avg then 'higher'
        when department_avg < company_avg then 'lower'
        else 'same'
    end as comparison
from
    (
        select
            department_id,
            avg(amount) as department_avg,
            date_format(pay_date, '%Y-%m') as pay_month
        from
            Salary s
            join Employee e on s.employee_id = e.employee_id
        group by
            department_id,
            pay_month
    ) department_salary
    join (
        select
            avg(amount) as company_avg,
            date_format(pay_date, '%Y-%m') as pay_month
        from
            Salary
        group by
            date_format(pay_date, '%Y-%m')
    ) company_salary on department_salary.pay_month = company_salary.pay_month;

# clean-up
drop table Salary;
drop table Employee;