HardNo. 0615LeetCode Premium
Average Salary: Departments VS Company — LeetCode 615 Python Solution
- Topics
- Database
- Reading time
- 3 min
- Source
- leetcode.com
The problem
For every pay month and department, compare the average salary paid by that department with the company-wide average for the same month, and report the month as YYYY-MM, the department id, and whether the department came out higher, lower or the same. The Salary table has one row per monthly payment: id (int, the primary key), employee_id (int), amount (int) and pay_date (date). The Employee table maps employee_id (int, the primary key) to department_id (int).
Example
Salary table: | id | employee_id | amount | pay_date | | -- | ----------- | ------ | ---------- | | 1 | 101 | 9000 | 2024-01-31 | | 2 | 102 | 6000 | 2024-01-31 | | 3 | 103 | 12000 | 2024-01-31 | | 4 | 101 | 9500 | 2024-02-29 | | 5 | 103 | 11500 | 2024-02-29 | Employee table: | employee_id | department_id | | ----------- | ------------- | | 101 | 1 | | 102 | 1 | | 103 | 2 | Result: | pay_month | department_id | comparison | | --------- | ------------- | ---------- | | 2024-01 | 1 | lower | | 2024-01 | 2 | higher | | 2024-02 | 1 | lower | | 2024-02 | 2 | higher | In January the company average is 9000 against 7500 for department 1 and 12000 for department 2, and in February it is 10500 against 9500 and 11500, so department 1 is under the line in both months and department 2 is over it.
Python solution
import pandas as pd
def compare_salaries(salary: pd.DataFrame, employee: pd.DataFrame) -> pd.DataFrame:
df = salary.merge(employee, on='employee_id')
df['pay_month'] = pd.to_datetime(df['pay_date']).dt.strftime('%Y-%m')
dept = df.groupby(['pay_month', 'department_id'])['amount'].mean().reset_index()
company = df.groupby('pay_month')['amount'].mean().rename('company_avg').reset_index()
merged = dept.merge(company, on='pay_month')
merged['comparison'] = merged.apply(
lambda r: 'higher' if r['amount'] > r['company_avg']
else ('lower' if r['amount'] < r['company_avg'] else 'same'),
axis=1,
)
return merged[['pay_month', 'department_id', 'comparison']]Complexity
- Time
- O(n log n)(typical)
- Space · auxiliary
- O(n)
Related problems
| No. | Problem | Difficulty |
|---|---|---|
| 0619 | Biggest Single Number | Easy |
| 0610 | Triangle Judgement | Easy |
| 0620 | Not Boring Movies | Easy |
| 0608 | Tree Node | Medium |
| 0607 | Sales Person | Easy |
| 0175 | Combine Two Tables | Easy |
Frequently asked questions
- How hard is LeetCode 615. Average Salary: Departments VS Company?
LeetCode 615. Average Salary: Departments VS Company is rated Hard on LeetCode.
- What topics does LeetCode 615. Average Salary: Departments VS Company cover?
LeetCode 615. Average Salary: Departments VS Company is tagged Database on LeetCode.
- Is LeetCode 615. Average Salary: Departments VS Company a premium problem?
Yes. LeetCode 615. Average Salary: Departments VS Company is a LeetCode Premium problem, so the full statement and test cases require a paid LeetCode subscription.