Average Salary: Departments VS Company — LeetCode 615 Python Solution

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

Related problems