LeetCode 184 – Department Highest Salary (SQL & Python) Solutions
This article is going to provide the solution to LeetCode 184 Department Highest Salary. It’s a medium difficulty question.
Write a solution to find employees who have the highest salary in each of the departments.
Return the result table in any order.
The result format is in the following example.
If you want to skip the article, check out the full YouTube video where I code the answer live.
LeetCode 184 SQL Solution
with salary_ranks as ( SELECT name, departmentId, salary, rank() over (partition by departmentId order by salary desc) as sal_rank from Employee)SELECT d.name as Department, sr.name as Employee, sr.salaryfrom salary_ranks srleft join Department d on d.id = sr.departmentIdwhere sal_rank = 1The Common Table Expression (salary_ranks) first selects the following columns: name, departmentId, salary. Each is in the Employee table.
It then calculates the rank of each employee within their department based on their salary.
This is done by using Rank() and partition by departmentId. We want the highest salaries first so order in descending.
The main query part of the code selects the necessary information from the previously defined CTE (salary_ranks) and performs a left join with the Department table on the id column.
It selects columns d.name (department name), sr.name (employee name), and sr.salary (employee salary).
The where clause filters the result set to only include rows where the sal_rank is equal to 1, meaning it selects only the highest-paid employee in each department.
LeetCode 184 Python Solution
import pandas as pddef department_highest_salary(employee: pd.DataFrame, department: pd.DataFrame) -> pd.DataFrame: employee['sal_rank'] = employee.groupby('departmentId')['salary'].rank(ascending=False, method='min') employee_dept = pd.merge(employee, department, left_on='departmentId', right_on='id', how='left') employee_dept_filtered = employee_dept[employee_dept['sal_rank'] == 1] employee_dept_filtered = employee_dept_filtered.rename(columns={'name_y': 'Department', 'name_x': 'Employee'}) return employee_dept_filtered[['Department', 'Employee', 'salary']]The supplied Python function department_highest_salary takes two pandas DataFrame objects, employee and department, as input and returns a new DataFrame containing the department name, highest-paid employee name, and their salary for each department
This first line calculates the rank of each employee’s salary within their department. It groups the employees by their department ID and then calculates the rank of their salary in descending order (ascending=False), using the minimum rank method (method=’min’). This mimics the SQL rank() window function used in the previous SQL code.
Next we merge the employee DataFrame with the department DataFrame based on the departmentId column in employee and the id column in department. It performs a left join (how=’left’), ensuring that all rows from the employee DataFrame are retained.
The next line filters the merged DataFrame to only include rows where the sal_rank column is equal to 1, i.e., the highest-paid employee in each department.
After that, rename the columns name_y and name_x to Department and Employee, respectively, to match the column names used in the expected output.
Lastly, returns a DataFrame containing only the Department, Employee, and salary columns from the filtered DataFrame.
