My solution + commentary


  • 0
    S
    # Write your MySQL query statement below
    SELECT
            departments.pay_month, 
            departments.department_id, 
            CASE WHEN departments.average > company.average THEN 'higher'
                WHEN departments.average = company.average THEN 'same'
                ELSE 'lower' END AS comparison
    FROM (SELECT LEFT(s1.pay_date, 7) AS pay_month,
                e1.department_id, 
                COALESCE(AVG (s1.amount), 0) AS average
        FROM salary s1
        LEFT JOIN employee e1 ON s1.employee_id = e1.employee_id
        GROUP BY pay_month, department_id
    ) departments
    LEFT JOIN (
        SELECT LEFT(pay_date, 7) AS pay_month, AVG(amount) AS average
        FROM salary 
        LEFT JOIN employee 
            ON salary.employee_id = employee.employee_id
        GROUP BY pay_month 
    ) company 
    ON company.pay_month = departments.pay_month 
    ORDER BY pay_month, department_id ASC
    

    It looks like the output in the problem description does not match the expected output of the test cases. In the problem description, the output appears to be in DESC order.


  • 0

    @siwest Right, the sample result does not match the test case result.


Log in to reply
 

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