Average Salary: Departments VS Company - Practice Coding Problems

Average Salary: Departments VS Company - Problem

Database Unknown

Average Salary: Departments VS Company

You're working as a data analyst for a large corporation and need to create a monthly salary comparison report. Your task is to compare each department's average salary with the company's overall average salary for every month.

Given two tables:

Salary table contains monthly salary records:
• id: Primary key
• employee_id: References Employee table
• amount: Salary amount for that month
• pay_date: The month/year of payment

Employee table contains department assignments:
• employee_id: Primary key
• department_id: Which department the employee belongs to

Your goal: For each month and department, determine if the department's average salary is "higher", "lower", or "same" compared to the company's average salary for that same month.

Output format: Return columns for pay_month, department_id, and comparison (higher/lower/same).

Input & Output

example_1.sql — Basic Scenario

$ 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 | 1 | | 3 | 2 |

› Output: | pay_month | department_id | comparison | |-----------|---------------|------------| | 2017-02 | 1 | lower | | 2017-02 | 2 | same | | 2017-03 | 1 | lower | | 2017-03 | 2 | higher |

💡 Note: For March 2017: Company avg = (9000+6000+10000)/3 = 8333.33. Dept 1 avg = (9000+6000)/2 = 7500 (lower), Dept 2 avg = 10000 (higher). For February 2017: Company avg = (7000+6000+8000)/3 = 7000. Dept 1 avg = (7000+6000)/2 = 6500 (lower), Dept 2 avg = 8000 (higher).

example_2.sql — Equal Averages

$ Input: Salary table: | id | employee_id | amount | pay_date | |----|-------------|--------|------------| | 1 | 1 | 8000 | 2017-03-31 | | 2 | 2 | 9000 | 2017-03-31 | | 3 | 3 | 8000 | 2017-03-31 | | 4 | 4 | 9000 | 2017-03-31 | Employee table: | employee_id | department_id | |-------------|---------------| | 1 | 1 | | 2 | 1 | | 3 | 2 | | 4 | 2 |

› Output: | pay_month | department_id | comparison | |-----------|---------------|------------| | 2017-03 | 1 | same | | 2017-03 | 2 | same |

💡 Note: Company average = (8000+9000+8000+9000)/4 = 8500. Department 1 average = (8000+9000)/2 = 8500. Department 2 average = (8000+9000)/2 = 8500. Both departments have the same average as the company.

example_3.sql — Single Employee Departments

$ Input: Salary table: | id | employee_id | amount | pay_date | |----|-------------|--------|------------| | 1 | 1 | 12000 | 2017-03-31 | | 2 | 2 | 5000 | 2017-03-31 | | 3 | 3 | 7000 | 2017-03-31 | Employee table: | employee_id | department_id | |-------------|---------------| | 1 | 1 | | 2 | 2 | | 3 | 3 |

› Output: | pay_month | department_id | comparison | |-----------|---------------|------------| | 2017-03 | 1 | higher | | 2017-03 | 2 | lower | | 2017-03 | 3 | lower |

💡 Note: Company average = (12000+5000+7000)/3 = 8000. Department 1: 12000 (higher than 8000). Department 2: 5000 (lower than 8000). Department 3: 7000 (lower than 8000). This shows how individual departments can vary significantly from company average.

Visualization

Tap to expand

Calculate Company Average: Sum all salaries for the month ÷ total employees2. Calculate Department Averages: Sum department salaries ÷ department employees3. Compare and Classify:• LOWER: Department avg < Company avg ($7,500 < $8,333)• HIGHER: Department avg > Company avg ($10,000 > $8,333)• SAME: Department avg = Company avg (exact match)💡 SQL Window Function Advantage:AVG() OVER (PARTITION BY month) calculates company average ONCE per month, not per department!

Understanding the Visualization

Collect Monthly Data

Gather all salary records for each month, similar to collecting all test scores

Calculate Company Average

Compute the overall company average salary for each month (like school average)

Calculate Department Averages

Compute average salary for each department in each month (like class averages)

Compare and Label

Compare each department's average to company average and label as higher/lower/same

Key Takeaway

🎯 Key Insight: Use window functions to calculate the company average once per month and share it across all department comparisons, avoiding expensive repeated subqueries.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

n salary records, dominated by sorting for window functions and GROUP BY

⚡ Linearithmic

Space Complexity

O(m * d)

Space for results where m is months and d is departments

✓ Linear Space

Constraints

1 ≤ employee_id ≤ 10⁴
1 ≤ department_id ≤ 100
1 ≤ salary amount ≤ 10⁶
pay_date format is YYYY-MM-DD
Each employee belongs to exactly one department
Employee can have multiple salary records (different months)

Asked in

a Amazon 35 G Google 28 ⊞ Microsoft 22 f Meta 18

The optimal solution uses SQL window functions to calculate company averages efficiently in a single pass. By using AVG() OVER (PARTITION BY month), we compute the company average once per month and make it available to all departments for that month. This approach is much more efficient than using subqueries, which would recalculate the company average for each department separately. The key insight is leveraging window functions with proper partitioning to avoid redundant calculations.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Multiple Subqueries)	O(n * m * d)	O(m * d)	Calculate company average separately for each department-month combination
Window Functions (Optimal)	O(n log n)	O(m * d)	Use window functions to calculate both averages in a single pass

Brute Force (Multiple Subqueries) — Algorithm Steps

For each unique department-month pair, calculate department average
For the same month, calculate company-wide average using subquery
Compare the two averages and determine higher/lower/same
Repeat this process for every department-month combination

Visualization

Tap to expand

Step-by-Step Walkthrough

Group by Month-Department

Calculate department averages for each month-department pair

Subquery for Company Average

For each group, run subquery to calculate company average for that month

Compare and Label

Compare department average with company average

Code -

solution.c — C

// SQL Solution using multiple subqueries (Brute Force)
char* sqlQuery = "\
SELECT \
    DATE_FORMAT(pay_date, '%Y-%m') as pay_month, \
    department_id, \
    CASE \
        WHEN dept_avg > company_avg THEN 'higher' \
        WHEN dept_avg < company_avg THEN 'lower' \
        ELSE 'same' \
    END as comparison \
FROM ( \
    SELECT \
        s.pay_date, \
        e.department_id, \
        AVG(s.amount) as dept_avg, \
        ( \
            SELECT AVG(amount) \
            FROM Salary s2 \
            WHERE DATE_FORMAT(s2.pay_date, '%Y-%m') = DATE_FORMAT(s.pay_date, '%Y-%m') \
        ) as company_avg \
    FROM Salary s \
    JOIN Employee e ON s.employee_id = e.employee_id \
    GROUP BY DATE_FORMAT(s.pay_date, '%Y-%m'), e.department_id \
) t \
ORDER BY pay_month, department_id;";

// Execute the query
// int result = mysql_query(connection, sqlQuery);

Time & Space Complexity

Time Complexity

⏱️

O(n * m * d)

n employees, m months, d departments - company average recalculated for each department

✓ Linear Growth

Space Complexity

O(m * d)

Space for storing results of each month-department combination

✓ Linear Space

Constraints

1 ≤ employee_id ≤ 10⁴
1 ≤ department_id ≤ 100
1 ≤ salary amount ≤ 10⁶
pay_date format is YYYY-MM-DD
Each employee belongs to exactly one department
Employee can have multiple salary records (different months)

28.5K Views

Medium Frequency

~18 min Avg. Time

847 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Brute Force (Multiple Subqueries) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler