Average Salary: Departments VS Company - Solution & Explanation

HardPremiumFree on FleetCodeDatabase5 min read

Problem Statement

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.

Approach Overview

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.

Solution

Code

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

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

Video Solution

LeetCode Hard 615 "Average Salary: Departments VS Company" Amazon Interview SQL Question Explanation • Everyday Data Science • 3,893 views views

Watch 2 more video solutions →

Frequently Asked Questions

Is Average Salary: Departments VS Company easy or hard?

Average Salary: Departments VS Company is classified as Hard because it requires multiple aggregation layers and correct grouping by month. Many candidates struggle with structuring the query and aligning department averages with company averages for the same period.

Average Salary: Departments VS Company Python/Java solution

This problem is designed for SQL rather than general programming languages. The solution typically uses MySQL queries with GROUP BY, CASE statements, and joins or window functions to compare department and company averages by month.

How to solve Average Salary: Departments VS Company in O(n)?

Join the Salary and Employee tables to attach departments, then compute department-level averages grouped by month and department_id. Compute company averages grouped by month in a second query. Join the two aggregated results on the month and compare averages using CASE to produce 'higher', 'lower', or 'same'.

What is the best approach for Average Salary: Departments VS Company?

The most reliable approach aggregates salaries twice: once for company-wide monthly averages and once for department-level monthly averages. After computing both grouped results, join them by month and compare the averages with a CASE expression. This method runs in O(n) time because it scans salary records once for aggregation.

Is Average Salary: Departments VS Company asked at Google/Amazon/Meta?

Database aggregation and comparison problems like this appear in SQL interviews at companies such as Amazon, Google, and Meta. They test understanding of GROUP BY, joins, and analytical comparisons across multiple aggregation levels.

What data structure is used in Average Salary: Departments VS Company?

The problem relies on relational database operations rather than traditional data structures. SQL GROUP BY aggregations, joins, and optionally window functions act as the core mechanisms for computing and comparing averages.

What is the time complexity of Average Salary: Departments VS Company?

The typical SQL solution runs in O(n) time where n is the number of salary records. Aggregation scans the table and groups by month and department. Space complexity is O(n) for intermediate grouped results depending on the database execution plan.

Ready to solve this problem?

Practice Average Salary: Departments VS Company with our built-in code editor and test cases.

Practice on FleetCode

Countries You Can Safely Invest In

Problem Info

DifficultyHard

Acceptance56.2%

Approaches1

Reading time5 min

Practice this problem

Open in Editor