Exercise 7：615.Average Salary: Departments VS Company

1.Description

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.

Table: salary

| id | employee_id | amount | pay_date   |
|----|-------------|--------|------------|
| 1  | 1           | 9000   | 2017-03-31 |
| 2  | 2           | 6000   | 2017-03-31 |
| 3  | 3           | 10000  | 2017-03-31 |
| 4  | 1           | 7000   | 2017-02-28 |
| 5  | 2           | 6000   | 2017-02-28 |
| 6  | 3           | 8000   | 2017-02-28 |

The employee_id column refers to the employee_id in the following table employee.

| employee_id | department_id |
|-------------|---------------|
| 1           | 1             |
| 2           | 2             |
| 3           | 2             |

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        |

2.Create Table and insert into values

create table if not exists leetcode.ex_615_salary
(
  id 						string,
  employee_id		string,
  amount 				int,
  pay_date 			date
) stored as orc ;
INSERT INTO table leetcode.ex_615_salary  VALUES
('1'  ,'1'   ,'9000'   , '2017-03-31' ),
('2'  ,'2'   ,'6000'   , '2017-03-31' ),
('3'  ,'3'   ,'10000'  , '2017-03-31' ),
('4'  ,'1'   ,'7000'   , '2017-02-28' ),
('5'  ,'2'   ,'6000'   , '2017-02-28' ),
('6'  ,'3'   ,'8000'   , '2017-02-28' )


create table if not exists leetcode.ex_615_employee
(employee_id	string, department_id	string) stored as orc ;
INSERT INTO table leetcode.ex_615_employee  VALUES
('1'   ,'1'),  ('2'   ,'2'), ('3'   ,'2')