00:00:00

Description

Editorial

Monthly Department vs Company Salary Benchmark

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

salary =

[{"id":1,"employee_id":1,"amount":9000,"pay_date":"2017-03-31"},{"id":2,"employee_id":2,"amount":6000,"pay_date":"2017-03-31"},{"id":3,"employee_id":3,"amount":10000,"pay_date":"2017-03-31"},{"id":4,"employee_id":1,"amount":7000,"pay_date":"2017-02-28"},{"id":5,"employee_id":2,"amount":6000,"pay_date":"2017-02-28"},{"id":6,"employee_id":3,"amount":8000,"pay_date":"2017-02-28"}]

employee =

[{"employee_id":1,"department_id":1},{"employee_id":2,"department_id":2},{"employee_id":3,"department_id":2}]

Loading problem...

101

00:00:00

Description

Editorial

Monthly Department vs Company Salary Benchmark

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

salary =

employee =

[{"employee_id":1,"department_id":1},{"employee_id":2,"department_id":2},{"employee_id":3,"department_id":2}]

Monthly Department vs Company Salary Benchmark

Hints

Monthly Department vs Company Salary Benchmark

Hints