My solution using LEFT JOIN


  • 2
    T
    SELECT d1.pay_month, d1.department_id, 
    CASE WHEN d1.department_avg > c1.company_avg THEN 'higher'
         WHEN d1.department_avg < c1.company_avg THEN 'lower'
         ELSE 'same'
    END AS 'comparison'
    FROM ((SELECT LEFT(s1.pay_date, 7) pay_month, e1.department_id, AVG(s1.amount) department_avg
    FROM salary s1
    JOIN employee e1 ON s1.employee_id = e1.employee_id
    GROUP BY pay_month, e1.department_id) d1
    LEFT JOIN (SELECT LEFT(pay_date, 7) pay_month, AVG(amount) company_avg
    FROM salary
    GROUP BY pay_month) c1 ON d1.pay_month = c1.pay_month)
    ORDER BY pay_month DESC, department_id;
    

Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.