stratascratch1-Salaries Differences

stratascratch1-Salaries Differences

Write a query that calculates the difference between the highest salaries found in the marketing and engineering departments. Output just the absolute difference in salaries.

Tables: db_employee, db_dept

--建表

use strata;

create table db_employee
(
id int
,first_name string
,last_name string
,salary int
,department_id int
)
row format delimited fields terminated by ',';
load data local inpath '/tmp/strata/db_employee.txt' overwrite into table db_employee;

create table db_dept
(
id int
,department string
)
row format delimited fields terminated by ',';
load data local inpath '/tmp/strata/db_dept.txt' overwrite into table db_dept;

--表结构
hive> select * from db_employee limit 5;
OK
db_employee.id  db_employee.first_name  db_employee.last_name   db_employee.salary      db_employee.department_id
10301   Keith   Morgan  27056   2
10302   Tyler   Booth   32199   3
10303   Clifford        Nguyen  32165   2
10304   Mary    Jones   49488   3
10305   Melissa Lucero  27024   3

hive> select * from db_dept;
OK
db_dept.id      db_dept.department
1       engineering
2       human resource
3       operation
4       marketing
5       sales
6       customer care

--结果输出
with t as
(select department_id,max(salary) as max_sal
from db_employee
where department_id in
(select id from db_dept where department regexp 'engineering|marketing')
group by department_id)
select abs(collect_set(max_sal)[0]-collect_set(max_sal)[1]) from t;