0615.Average Salary Departments VS Company

[junaid1068]

With values_before_comparison as (
Select e.department_id ,
To_char(pay_date,'yyyy-mm') as pay_month ,
Avg(Amount) over(Partition by e.department_id, To_char(pay_date,'yyyy-mm') ) as Department_Average,
Avg(Amount) over(Partition by To_char(pay_date,'yyyy-mm') ) as company_Average
From Salary as s join Employee as e on s.employee_id = e.employee_id
)

Select distinct pay_month , department_id ,
Case when Department_Average > company_Average then 'higher'
when Department_Average < company_Average then 'lower'
when Department_Average = company_Average then 'same' end as comparison
From values_before_comparison