Average Salary: Departments VS Company - Problem

Given two tables Salary and Employee, find the comparison result (higher/lower/same) of the average salary of employees in each department to the company's average salary.

The Salary table contains employee salary information for each month, and the Employee table contains department assignment for each employee.

Return the result table showing each department and pay month with their comparison status.

Table Schema

Salary
Column Name Type Description
id PK int Primary key for salary record
employee_id int Foreign key referencing Employee table
amount int Salary amount for the month
pay_date date Payment date (month/year)
Primary Key: id
Employee
Column Name Type Description
employee_id PK int Primary key for employee
department_id int Department identifier
Primary Key: employee_id

Input & Output

Example 1 — Basic Department Comparison
Input Tables:
Salary
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
employee_id department_id
1 1
2 1
3 2
Output:
pay_month department_id comparison
2017-02-01 1 same
2017-02-01 2 same
2017-03-01 1 lower
2017-03-01 2 higher
💡 Note:

For February 2017: Department 1 average = (7000+6000)/2 = 6500, Department 2 average = 8000, Company average = (7000+6000+8000)/3 = 7000. Department 1 is lower, Department 2 is higher than company average.

For March 2017: Department 1 average = (9000+6000)/2 = 7500, Department 2 average = 10000, Company average = (9000+6000+10000)/3 = 8333.33. Department 1 is lower, Department 2 is higher than company average.
Example 2 — Same Average Case
Input Tables:
Salary
id employee_id amount pay_date
1 1 8000 2017-01-31
2 2 8000 2017-01-31
Employee
employee_id department_id
1 1
2 2
Output:
pay_month department_id comparison
2017-01-01 1 same
2017-01-01 2 same
💡 Note:

Both departments have the same average salary (8000) as the company average (8000), so the comparison result is 'same' for both departments.

Constraints

  • 1 ≤ employee_id ≤ 1000
  • 1 ≤ department_id ≤ 100
  • amount ≥ 0
  • All dates are valid dates in YYYY-MM-DD format

Visualization

Tap to expand
Department vs Company Average Salary AnalysisInput TablesSalaryemp_idamount19000Employeeemp_iddept_id11JOIN &COMPAREResultpay_monthdept_idcomparison2017-03-011lower2017-03-012higherCompares each department average to company average per month
Understanding the Visualization
1
Join
Connect salary and employee tables
2
Aggregate
Calculate department and company averages
3
Compare
Determine higher/lower/same status
Key Takeaway
🎯 Key Insight: Window functions enable efficient comparison between group statistics and global statistics in a single query

Related Problems

Asked in
Amazon 28 Microsoft 22 Google 18
32.4K Views
Medium Frequency
~18 min Avg. Time
892 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