Department Highest Salary — LeetCode 184 Python Solution

The problem

For every department, find the employees paid the highest salary in that department, listing all of them when several are tied at the top. 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 the department name, the employee name and the salary as Department, Employee and Salary.

Example

Input

Employee(id, name, salary, departmentId) = (1, 'Nia', 90000, 1), (2, 'Omar', 80000, 2), (3, 'Priya', 90000, 1), (4, 'Quinn', 85000, 1); Department(id, name) = (1, 'IT'), (2, 'Sales')

Output

('IT', 'Nia', 90000), ('Sales', 'Omar', 80000), ('IT', 'Priya', 90000)

Explanation

IT's top salary of 90000 is shared by Nia and Priya, so both are returned; Omar is the highest paid in Sales.

Python solution

import pandas as pd


def department_highest_salary(
    employee: pd.DataFrame, department: pd.DataFrame
) -> pd.DataFrame:
    # Merge the two tables on departmentId and department id
    merged = employee.merge(department, left_on='departmentId', right_on='id')

    # Find the maximum salary for each department
    max_salaries = merged.groupby('departmentId')['salary'].transform('max')

    # Filter employees who have the highest salary in their department
    top_earners = merged[merged['salary'] == max_salaries]

    # Select required columns and rename them
    result = top_earners[['name_y', 'name_x', 'salary']].copy()
    result.columns = ['Department', 'Employee', 'Salary']

    return result

Complexity

Related problems