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 lower
2017-02-01 2 higher
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
Average Salary: Departments VS Company INPUT Salary Table id emp_id amount pay_date 1 1 9000 2017-03 2 2 6000 2017-03 3 3 10000 2017-03 4 1 7000 2017-02 5 2 6000 2017-02 Employee Table emp_id dept_id 1 1 2 2 3 2 Join tables on emp_id to link salary with dept ALGORITHM STEPS 1 Calculate Company Avg AVG(amount) per month Mar: (9k+6k+10k)/3=8333 2 Calculate Dept Avg AVG per dept per month Dept1 Mar: 9000 Dept2 Mar: (6k+10k)/2=8000 3 Compare Averages CASE WHEN logic dept_avg > comp_avg --> 'higher' dept_avg < comp_avg 4 Format Output DATE_FORMAT for pay_month GROUP BY dept, month FINAL RESULT Comparison Result Table pay_month dept comp 2017-03 1 higher 2017-03 2 lower 2017-02 1 same 2017-02 2 same March Comparison: Company Avg: 8333 Dept1: 9000 > 8333 [OK] Dept2: 8000 < 8333 Status Legend: = higher than company = lower than company Key Insight: Use CTEs or subqueries to calculate both company-wide and department-level averages separately. Then JOIN these results and use CASE WHEN to compare and determine if each department's average is 'higher', 'lower', or 'same' compared to the company average for each pay period. TutorialsPoint - Average Salary: Departments VS Company | Optimal Solution

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