Amazon SQL interview Questions – Leetcode 615 – Average Salary: Departments VS Company

Problem Description –

Given two tables as below, write a query to display the comparison result (higher/lower/same) of the average salary of employees in a department to the company’s average salary.

Table – Salary

The employee_id column refers to the employee_id in the following table employee.

Table – employee

So for the sample data above, the result is:

Explanation

In March, the company’s average salary is (9000+6000+10000)/3 = 8333.33…

The average salary for department ‘1’ is 9000, which is the salary of employee_id ‘1’ since there is only one employee in this department. So the comparison result is ‘higher’ since 9000 > 8333.33 obviously.

The average salary of department ‘2’ is (6000 + 10000)/2 = 8000, which is the average of employee_id ‘2’ and ‘3’. So the comparison result is ‘lower’ since 8000 < 8333.33.

With he same formula for the average salary comparison in February, the result is ‘same’ since both the department ‘1’ and ‘2’ have the same average salary with the company, which is 7000.

Difficulty Level – Hard

Problem Link – https://leetcode.com/problems/average-salary-departments-vs-company/

Solution –

WITH company AS(
    SELECT 
        date_format(pay_date, '%Y-%m') as month,
        AVG(amount) as company_avg_salary
    FROM salary
    GROUP BY 1
),
Department AS (
    SELECT 
        date_format(pay_date, '%Y-%m') as month,
        e.department_id,
        AVG(s.amount) as dept_avg_salary
    FROM salary as s JOIN employee as e
    ON s.employee_id = e.employee_id
    GROUP BY 1, 2
)

SELECT
    d.month as pay_month,
    d.department_id,
    CASE WHEN d.dept_avg_salary > c.company_avg_salary THEN 'higher'
        WHEN d.dept_avg_salary < c.company_avg_salary THEN 'lower'
        ELSE 'same'
    END AS comparison
FROM Department as d LEFT JOIN company as c
ON d.month = c.month
ORDER BY 1 DESC

Let’s breakdown the problem.

WITH company AS(
    SELECT 
        date_format(pay_date, '%Y-%m') as month,
        AVG(amount) as company_avg_salary
    FROM salary
    GROUP BY 1
)

SELECT *
FROM company

To get the year and month from the date, we used the mysql DATE_FORMAT() function and then group the data by year_month and calculated the average salary for the all the employees in the company.

Next we need to calculate the average salary for all the employee by Department grouped by year_month.

WITH Department AS (
    SELECT 
        date_format(pay_date, '%Y-%m') as month,
        e.department_id,
        AVG(s.amount) as dept_avg_salary
    FROM salary as s JOIN employee as e
    ON s.employee_id = e.employee_id
    GROUP BY 1, 2
)

SELECT *
FROM Department

Here, we first grouped the data by year_month and then by department and calculated the average salary of the employees.

Next we need to join both the table on year_month column.

WITH company AS(
    SELECT 
        date_format(pay_date, '%Y-%m') as month,
        AVG(amount) as company_avg_salary
    FROM salary
    GROUP BY 1
),
Department AS (
    SELECT 
        date_format(pay_date, '%Y-%m') as month,
        e.department_id,
        AVG(s.amount) as dept_avg_salary
    FROM salary as s JOIN employee as e
    ON s.employee_id = e.employee_id
    GROUP BY 1, 2
)

SELECT *
FROM Department as d LEFT JOIN company as c
ON d.month = c.month

Now, all we have to do is apply the logic using CASE statement to get the desired result.

    CASE WHEN d.dept_avg_salary > c.company_avg_salary THEN 'higher'
        WHEN d.dept_avg_salary < c.company_avg_salary THEN 'lower'
        ELSE 'same' END AS comparison

For first row the department average saalry is ‘greater’ than the company average salary, so sql will assign higher, for row 2 it will be ‘lower’, and for 3 and 4 it will be ‘same’ as average salary of department and company is same.