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;
Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.