Click here to see the full article post
@lleiou Thanks for the feedback. It has been corrected.
If you are using PostgreSQL, you could write the solution mentioned above in a more readable way using CTEs (common table expressions):
with company_avg as ( select to_char(pay_date, 'YYYY-MM') "pay_month", avg(amount) "avg_company" from l615_salary join l615_employee using(employee_id) group by pay_month ), dept_avg as ( select to_char(pay_date, 'YYYY-MM') "pay_month", department_id, avg(amount) "avg_dept" from l615_salary join l615_employee using(employee_id) group by pay_month, department_id ) select pay_month, department_id, case when avg_dept > avg_company then 'higher' when avg_dept < avg_company then 'lower' when avg_dept = avg_company then 'same' end "comparison" from company_avg join dept_avg using(pay_month) order by pay_month desc, department_id ;
You could write CTEs If you are using PostgreSQL:
WITH company_salary AS (
GROUP BY pay_date
dept_salary AS (
FROM salary s JOIN employee e ON s.employee_id = e.employee_id
GROUP BY pay_date, department_id
CASE WHEN s2.dept_avg < s1.comp_avg
WHEN s2.dept_avg > s1.comp_avg
ELSE 'same' END
FROM company_salary s1 JOIN dept_salary s2 ON s1.pay_date = s2.pay_date;
with dataset as (
avg(amount) over (partition by trunc(pay_date,'mm') ) - avg(amount) over (partition by trunc(pay_date,'mm'),department_id ) as salary_ind,
department_id, trunc(pay_date,'mm') as pay_month
from salary a
join employee e on a.employee_id =e.employee_id
select distinct pay_month,department_id,
case when salary_ind > 0 then 'lower'
case when salary_ind < 0 then 'higher '
else ('same') end
) end as comparison
Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.