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;
    

  • 0

    Same idea, but I'm more used to where clause:)
    is LEFT JOIN really necessary here?

    select t1.pay_month as pay_month, department_id,
    case
    when t1.average_month>t2.average_month then 'higher'
    when t1.average_month<t2.average_month then 'lower'
    else 'same'
    end
    as comparison
    from
    (
    select LEFT(pay_date, 7) as pay_month, department_id, avg(amount) as average_month
    from employee, salary
    where employee.employee_id = salary.employee_id
    group by pay_month, department_id
    ) t1,
    (
    select LEFT(pay_date, 7) as pay_month, avg(amount) as average_month
    from salary
    group by pay_month
    ) t2
    where t1.pay_month = t2.pay_month
    order by department_id, pay_month


Log in to reply
 

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