类型：困难

题目

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.

  1. drop table salary;
  2. Create table salary(Id int,employee_id int,amount int,pay_date date);
  3. insert into salary values(1,1,9000,'2017-03-31');
  4. insert into salary values(2,2,6000,'2017-03-31');
  5. insert into salary values(3,3,10000,'2017-03-31');
  6. insert into salary values(4,1,7000,'2017-02-28');
  7. insert into salary values(5,2,6000,'2017-02-28');
  8. insert into salary values(6,3,8000,'2017-02-28');
  10. drop table employee
  11. Create table employee(Id int,department_id int);
  12. insert into employee values(1,1);
  13. insert into employee values(2,2);
  14. insert into employee values(3,2);
  16. Table: salary
  17. | id | employee_id | amount | pay_date   |
  18. |----|-------------|--------|------------|
  19. | 1  | 1           | 9000   | 2017-03-31 |
  20. | 2  | 2           | 6000   | 2017-03-31 |
  21. | 3  | 3           | 10000  | 2017-03-31 |
  22. | 4  | 1           | 7000   | 2017-02-28 |
  23. | 5  | 2           | 6000   | 2017-02-28 |
  24. | 6  | 3           | 8000   | 2017-02-28 |
  26. The employee_id column refers to the employee_id in the following table employee.
  27. | employee_id | department_id |
  28. |-------------|---------------|
  29. | 1           | 1             |
  30. | 2           | 2             |
  31. | 3           | 2             |

So for the sample data above, the result is:

  1. | pay_month | department_id | comparison  |
  2. |-----------|---------------|-------------|
  3. | 2017-03   | 1             | higher      |
  4. | 2017-03   | 2             | lower       |
  5. | 2017-02   | 1             | same        |
  6. | 2017-02   | 2             | same        |

Explanation
**
In March, the company’s average salary is (9000+6000+10000)/3 = 8333.33…

The average salary for department ‘1’ is 9000, which is the salary of employee_id ‘1’ since there is only one employee in this department. So the comparison result is ‘higher’ since 9000 > 8333.33 obviously.

The average salary of department ‘2’ is (6000 + 10000)/2 = 8000, which is the average of employee_id ‘2’ and ‘3’. So the comparison result is ‘lower’ since 8000 < 8333.33.

With he same formula for the average salary comparison in February, the result is ‘same’ since both the department ‘1’ and ‘2’ have the same average salary with the company, which is 7000.

大致内容：看看一个部门的平均工资，跟公司总体相比，是更高还是更低。