Monthly Department vs Company Salary Benchmark

Medium

A payroll analytics system stores monthly compensation in two relational tables: Salary(id, employee_id, amount, pay_date) Employee(employee_id, department_id) Each Salary row records one employee payout on a specific date. For each calendar month, compare every department's average salary to that month's company-wide average salary. Return one row per (pay_month, department_id) that has salary data, with: pay_month: month in YYYY-MM format department_id comparison: "higher" if department average > company average "lower" if department average < company average "same" if both averages are equal Supported submissions: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

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

Output

[
  {"pay_month":"2017-02","department_id":1,"comparison":"same"},
  {"pay_month":"2017-02","department_id":2,"comparison":"same"},
  {"pay_month":"2017-03","department_id":1,"comparison":"higher"},
  {"pay_month":"2017-03","department_id":2,"comparison":"lower"}
]

Explanation

In March, company average is 8333.33..., so department 1 (9000) is higher and department 2 (8000) is lower. In February, both departments match the company average of 7000.

Example 2

Input

Salary:
| id | employee_id | amount | pay_date   |
|----|-------------|--------|------------|
| 10 | 11          | 5000   | 2024-06-05 |
| 11 | 12          | 5000   | 2024-06-18 |
| 12 | 13          | 5000   | 2024-06-25 |

Employee:
| employee_id | department_id |
|-------------|---------------|
| 11          | 100           |
| 12          | 101           |
| 13          | 101           |

Output

[
  {"pay_month":"2024-06","department_id":100,"comparison":"same"},
  {"pay_month":"2024-06","department_id":101,"comparison":"same"}
]

Explanation

All salaries are equal in this month, so each department average equals the company average.

Example 3

Input

Salary:
| id | employee_id | amount | pay_date   |
|----|-------------|--------|------------|
| 21 | 31          | 8000   | 2025-01-12 |
| 22 | 32          | 6500   | 2025-01-20 |
| 23 | 31          | 9000   | 2025-02-10 |
| 24 | 33          | 7000   | 2025-02-15 |

Employee:
| employee_id | department_id |
|-------------|---------------|
| 31          | 10            |
| 32          | 20            |
| 33          | 20            |

Output

[
  {"pay_month":"2025-01","department_id":10,"comparison":"higher"},
  {"pay_month":"2025-01","department_id":20,"comparison":"lower"},
  {"pay_month":"2025-02","department_id":10,"comparison":"higher"},
  {"pay_month":"2025-02","department_id":20,"comparison":"lower"}
]

Explanation

Each month is evaluated independently. Department 10 has the larger average in both January and February.

Constraints

1 <= number of rows in Salary <= 300,000
1 <= number of rows in Employee <= 100,000
Salary.id is unique
Employee.employee_id is unique
0 <= Salary.amount <= 1,000,000,000
Salary.pay_date is a valid date
Each Salary.employee_id references Employee.employee_id in valid datasets
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return exactly [pay_month, department_id, comparison]

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

[ {"pay_month":"2017-02","department_id":1,"comparison":"same"}, {"pay_month":"2017-02","department_id":2,"comparison":"same"}, {"pay_month":"2017-03","department_id":1,"comparison":"higher"}, {"pay_month":"2017-03","department_id":2,"comparison":"lower"} ]

Salary: | id | employee_id | amount | pay_date | |----|-------------|--------|------------| | 10 | 11 | 5000 | 2024-06-05 | | 11 | 12 | 5000 | 2024-06-18 | | 12 | 13 | 5000 | 2024-06-25 | Employee: | employee_id | department_id | |-------------|---------------| | 11 | 100 | | 12 | 101 | | 13 | 101 |

Salary: | id | employee_id | amount | pay_date | |----|-------------|--------|------------| | 21 | 31 | 8000 | 2025-01-12 | | 22 | 32 | 6500 | 2025-01-20 | | 23 | 31 | 9000 | 2025-02-10 | | 24 | 33 | 7000 | 2025-02-15 | Employee: | employee_id | department_id | |-------------|---------------| | 31 | 10 | | 32 | 20 | | 33 | 20 |

[ {"pay_month":"2025-01","department_id":10,"comparison":"higher"}, {"pay_month":"2025-01","department_id":20,"comparison":"lower"}, {"pay_month":"2025-02","department_id":10,"comparison":"higher"}, {"pay_month":"2025-02","department_id":20,"comparison":"lower"} ]