Department Top Three Salaries — LeetCode 185 Python Solution

The problem

For every department, list the employees earning one of the three highest distinct salaries in that department; because a salary shared by several people counts once, a department can return more than three employees. Employee has id (int, primary key), name (varchar), salary (int) and departmentId (int) referencing a department; Department has id (int, primary key) and name (varchar). Return Department, Employee and Salary.

Example

Input

Employee(id, name, salary, departmentId) = (1, 'Ada', 85000, 1), (2, 'Bo', 85000, 1), (3, 'Cy', 70000, 1), (4, 'Di', 60000, 1), (5, 'Eli', 50000, 1), (6, 'Fay', 90000, 2); Department(id, name) = (1, 'IT'), (2, 'Sales')

Output

('IT', 'Ada', 85000), ('IT', 'Bo', 85000), ('IT', 'Cy', 70000), ('IT', 'Di', 60000), ('Sales', 'Fay', 90000)

Explanation

The top three distinct IT salaries are 85000, 70000 and 60000, and 85000 is shared, so four IT employees qualify.

Python solution

import pandas as pd


def top_three_salaries(
    employee: pd.DataFrame, department: pd.DataFrame
) -> pd.DataFrame:
    salary_cutoff = (
        employee.drop_duplicates(["salary", "departmentId"])
        .groupby("departmentId")["salary"]
        .nlargest(3)
        .groupby("departmentId")
        .min()
    )
    employee["Department"] = department.set_index("id")["name"][
        employee["departmentId"]
    ].values
    employee["cutoff"] = salary_cutoff[employee["departmentId"]].values
    return employee[employee["salary"] >= employee["cutoff"]].rename(
        columns={"name": "Employee", "salary": "Salary"}
    )[["Department", "Employee", "Salary"]]

Complexity

Related problems