615 Average Salary: Departments vs Company

Description

Table: Salary

• In SQL, id is the primary key column for this table.
• Each row of this table indicates the salary of an employee in one month.
• employee_id is a foreign key (reference column) from the Employee table.

Table: Employee

• In SQL, employee_id is the primary key column for this table.
• Each row of this table indicates the department of an employee.

Problem Statement

Find the comparison result (higher/lower/same) of the average salary of employees in a department to the company’s average salary.

Return the result table in any order.

The result format is in the following example.

Example 1:

Input:

• Salary table:

• Employee table:

Output:

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 the 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.

Solution

This is a complicated problem. Basically, what you need to find the is the average of department salary per month and average of company salary per month and you want to compare those two values. The problem can be divided into following subproblems.

1. Find company average salary per month.
2. Find department average salary per month.
3. Compare the two averages and create comparison column.

1. Company average salary per month

The Salary table contains information about the salary of employees per month. Here, you simply need to aggregate the salary per month. Notice that you have the pay_date as a date column. You can use the DATE_FORMAT() function to extract the year and month from this column using DATE_FORMAT(pay_date, '%Y-%m')

1SELECT
2    DATE_FORMAT(pay_date, '%Y-%m') AS pay_month, 
3    AVG(amount) AS company_average
4    FROM Salary
5    GROUP BY DATE_FORMAT(pay_date, '%Y-%m');

2. Department average salary per month

The department information is stored in Employee table. So, in order to find the average salary per department per month, you have to join Salary table with Employee table. In this case, you will have to aggregate the amount field by pay_month and department_id columns.

1SELECT
2    DATE_FORMAT(pay_date, '%Y-%m') AS pay_month,
3    department_id,
4    AVG(amount) AS department_average
5    FROM Salary
6    JOIN Employee
7    ON Salary.employee_id = Employee.employee_id
8    GROUP BY DATE_FORMAT(pay_date, '%Y-%m'), department_id;

3. Compare the two averages

Now, that you have two averages, you can create temporary views from these two tables. Next, you can join these two temporary views using pay_month column. Once you’ve joined these two views, you can add the comparison column which will use CASE WHEN statement to compare the department_average column with company_average column from the temporary views.

 1SELECT 
 2    c.pay_month, d.department_id,
 3    CASE
 4        WHEN d.department_average > c.company_average THEN 'higher'
 5        WHEN d.department_average < c.company_average THEN 'lower'
 6        ELSE 'same'
 7        END AS comparison
 8    FROM department_averages d
 9    JOIN company_averages c
10    ON d.pay_month = c.pay_month;

Combining all these subproblems, you can get the final result as below.

 1WITH company_averages AS (
 2    SELECT
 3        DATE_FORMAT(pay_date, '%Y-%m') AS pay_month, 
 4        AVG(amount) AS company_average
 5        FROM Salary
 6        GROUP BY DATE_FORMAT(pay_date, '%Y-%m')
 7), department_averages AS (
 8    SELECT
 9        DATE_FORMAT(pay_date, '%Y-%m') AS pay_month,
10        department_id,
11        AVG(amount) AS department_average
12        FROM Salary
13        JOIN Employee
14        ON Salary.employee_id = Employee.employee_id
15        GROUP BY DATE_FORMAT(pay_date, '%Y-%m'), department_id
16) SELECT 
17    c.pay_month, d.department_id,
18    CASE
19        WHEN d.department_average > c.company_average THEN 'higher'
20        WHEN d.department_average < c.company_average THEN 'lower'
21        ELSE 'same'
22        END AS comparison
23    FROM department_averages d
24    JOIN company_averages c
25    ON d.pay_month = c.pay_month;