Department vs. Company Salary

Department vs. Company Salary

문제

1. 2024년 3월 기준

2. 각 부서의 평균 급여를 계산

3. 회사 전체 평균 급여와 비교

4. 결과를 higher, lower, same

5. 부서 ID, 급여 지급 월, 비교 결과

 

풀이

with temp_01 as (
  select e.department_id,
  to_char("payment_date", 'MM-YYYY') as payment_date,
  avg(s.amount) as avg_salary
  from employee e 
  inner join salary s on e.employee_id = s.employee_id
  and extract(year from s.payment_date) = '2024'
  and extract(month from s.payment_date) = '03'
  group by 1, 2
),
temp_02 as (
select avg(amount) AS country_avg
from salary 
where extract(year from payment_date) = '2024'
and extract(month from payment_date) = '03'
)
select 
  department_id, 
  payment_date,
  case when avg_salary > (select country_avg from temp_02) then 'higher'
    when avg_salary = (select country_avg from temp_02) then 'same'
    when avg_salary < (select country_avg from temp_02) then 'lower' end as comparison
from temp_01

1️⃣ CTE1; temp_01: 2024-03 각 부서명 평균 급여

2️⃣ CTE2; temp_02: 2024-03 회사 평균 급여 

3️⃣ case when: 부서명 평균 급여, 회사  평균 급여 비교