Back to questions
Department vs. Company Salary [FAANG SQL Interview Question]
You work as a data analyst for a FAANG company that tracks employee salaries over time. The company wants to understand how the average salary in each department compares to the company's overall average salary each month.
Write a query to compare the average salary of employees in each department to the company's average salary for March 2024. Return the comparison result as 'higher', 'lower', or 'same' for each department. Display the department ID, payment month (in MM-YYYY format), and the comparison result.
Schema:
| column_name | type | description |
|---|---|---|
| employee_id | integer | The unique ID of the employee. |
| name | string | The full name of the employee. |
| salary | integer | The salary of the employee. |
| department_id | integer | The department ID of the employee. |
| manager_id | integer | The manager ID of the employee. |
Example Input:
| employee_id | name | salary | department_id | manager_id |
|---|---|---|---|---|
| 1 | Emma Thompson | 3800 | 1 | 6 |
| 2 | Daniel Rodriguez | 2230 | 1 | 7 |
| 3 | Olivia Smith | 7000 | 1 | 8 |
| 5 | Sophia Martinez | 1750 | 1 | 11 |
Schema:
| column_name | type | description |
|---|---|---|
| salary_id | integer | A unique ID for each salary record. |
| employee_id | integer | The unique ID of the employee. |
| amount | integer | The salary of the employee. |
| payment_date | datetime | The date and time when the salary was paid to the employee. |
Example Input:
| salary_id | employee_id | amount | payment_date |
|---|---|---|---|
| 1 | 1 | 3800 | 01/31/2024 00:00:00 |
| 2 | 2 | 2230 | 01/31/2024 00:00:00 |
| 3 | 3 | 7000 | 01/31/2024 00:00:00 |
| 4 | 4 | 6800 | 01/31/2024 00:00:00 |
| 5 | 5 | 1750 | 01/31/2024 00:00:00 |
Example Output:
| department_id | payment_date | comparison |
|---|---|---|
| 1 | 01-2024 | lower |
The output indicates that the average salary of Department 1 is lower than the company's average salary for January 2024.
The dataset you are querying against may have different input & output - this is just an example!
Solution
Step 1: Calculate Company's Average Salary for March 2024
In this step, we calculate the average salary of all employees in the company for March 2024.
First, we filter the data to consider only the records for March 2024 in the clause.
Subsequently, we use the AVG() function to calculate the average salary of all employees. It aggregates the column which represents the salary of each employee and computes the average.
This query will be wrapped in a CTE called 'company_avg' to be used in the following step. Feel free to name your CTE as appropriate.
Step 2: Calculate Department's Average Salary for March 2024
Next, we compute the average salary of employees in each department for March 2024.
We join the table with the table using an on the column. This allows us to associate each salary record with its corresponding department.
Similar to Step 1, we filter the data to consider only the records for March 2024 using the clause. The function is again used to compute the average salary of employees in each department.
This query will be wrapped in a CTE called 'dept_avg' to be used in the final query.
Step 3: Compare Department's Average Salaries with Company's Average Salaries
In the final step, we compare the average salary of each department with the company's average salary.
Using an , we join the CTE with the CTE based on the columns. This allows us to compare the department-wise average salaries with the company's overall average salary.
We use the CASE statement to compare the average salary of each department with the company's average salary. If the department's average salary is higher, it is labelled as 'higher'. If it's lower, it is labelled as 'lower'. Otherwise, it is labelled as 'same'.
Additionally, we utilize the TO_CHAR() function to convert the column to the desired format of 'MM-YYYY'.
PostgreSQL 14