Table: Salary
+-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | employee_id | int | | amount | int | | pay_date | date | +-------------+------+ 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
+---------------+------+ | Column Name | Type | +---------------+------+ | employee_id | int | | department_id | int | +---------------+------+ In SQL, employee_id is the primary key column for this table. Each row of this table indicates the department of an employee.
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: +----+-------------+--------+------------+ | id | employee_id | amount | pay_date | +----+-------------+--------+------------+ | 1 | 1 | 9000 | 2017/03/31 | | 2 | 2 | 6000 | 2017/03/31 | | 3 | 3 | 10000 | 2017/03/31 | | 4 | 1 | 7000 | 2017/02/28 | | 5 | 2 | 6000 | 2017/02/28 | | 6 | 3 | 8000 | 2017/02/28 | +----+-------------+--------+------------+ Employee table: +-------------+---------------+ | employee_id | department_id | +-------------+---------------+ | 1 | 1 | | 2 | 2 | | 3 | 2 | +-------------+---------------+ Output: +-----------+---------------+------------+ | pay_month | department_id | comparison | +-----------+---------------+------------+ | 2017-02 | 1 | same | | 2017-03 | 1 | higher | | 2017-02 | 2 | same | | 2017-03 | 2 | lower | +-----------+---------------+------------+ 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.
Problem Overview: Each salary record contains a payment date and employee. The task is to compare the average salary of each department with the overall company average for the same month. For every month and department pair, return whether the department average is higher, lower, or same compared to the company average.
Approach 1: Monthly Aggregation + Join (O(n) time, O(n) space)
This approach computes two aggregated datasets: the company-wide monthly average salary and the department-level monthly average salary. First join the Salary and Employee tables so each payment record includes the department. Then group rows by DATE_FORMAT(pay_date, '%Y-%m') and department_id to calculate the department’s monthly average. In a separate aggregation, group only by month to calculate the company average. Finally join these two aggregated results on the month and compare the averages using a CASE expression to label the result as higher, lower, or same. The query performs linear scans over the salary records, giving O(n) time complexity with O(n) space for the grouped results.
This pattern is common in database interview problems where you compute metrics at multiple aggregation levels. The key insight is separating the company-level aggregation from the department-level aggregation, then merging them for comparison.
Approach 2: Window Function Comparison (O(n) time, O(n) space)
A more compact SQL solution uses window functions. After joining employee and salary tables, compute the department average with AVG(amount) OVER(PARTITION BY month, department_id) and the company average with AVG(amount) OVER(PARTITION BY month). Each row now contains both averages. Use CASE to compare them and select distinct month–department combinations. Window functions remove the need for separate aggregation subqueries but rely on the database’s window engine. Time complexity remains O(n) since every row is processed once, with O(n) intermediate storage for window calculations.
Window-based solutions are common in modern SQL systems and show strong familiarity with analytical queries. However, many interviewers still expect the explicit aggregation approach because it demonstrates clear control over grouping and joins.
Recommended for interviews: Use the monthly aggregation + join method. It clearly shows how to compute metrics at different granularities and compare them. A brute-force mindset—calculating averages per group separately—demonstrates understanding, while the optimized grouped query shows real SQL problem-solving skill.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Monthly Aggregation + Join | O(n) | O(n) | Standard SQL interviews; clear separation of department vs company averages |
| Window Function Comparison | O(n) | O(n) | When the database supports analytic functions and you want a concise query |
| Nested Subqueries with Aggregation | O(n) | O(n) | Alternative when window functions are unavailable |
LeetCode Hard 615 "Average Salary: Departments VS Company" Amazon Interview SQL Question Explanation • Everyday Data Science • 3,617 views views
Watch 2 more video solutions →Practice Average Salary: Departments VS Company with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor