Description
Table: Employee
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| id | int |
| name | varchar |
| salary | int |
| departmentId | int |
+--------------+---------+
id is the primary key (column with unique values) for this table.
departmentId is a foreign key (reference columns) of the ID from theDepartmenttable.
Each row of this table indicates the ID, name, and salary of an employee. It also contains the ID of their department.
Table: Department
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
id is the primary key (column with unique values) for this table. It is guaranteed that department name is notNULL.
Each row of this table indicates the ID of a department and its name.
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.
Examples:
Example 1:
Input:
Employee table:
+----+-------+--------+--------------+
| id | name | salary | departmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department table:
+----+-------+
| id | name |
+----+-------+
| 1 | IT |
| 2 | Sales |
+----+-------+
Output:
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
| IT | Max | 90000 |
+------------+----------+--------+Solution in Pandas
Python
import pandas as pd
def department_highest_salary(employee: pd.DataFrame, department: pd.DataFrame) -> pd.DataFrame:
# Step 1: Merge the employee and department DataFrames on the departmentId
merged_df = pd.merge(employee, department, left_on='departmentId', right_on='id', how='inner')
# Step 2: Group by department name and find the maximum salary in each department
max_salaries = merged_df.groupby('departmentId')['salary'].max().reset_index()
# Step 3: Merge back with the original merged_df to filter employees with max salaries
highest_salary_employees = pd.merge(merged_df, max_salaries, on=['departmentId', 'salary'], how='inner')
# Step 4: Select the required columns and rename them to match the expected output
result_df = highest_salary_employees[['name_y', 'name_x', 'salary']]
result_df.columns = ['Department', 'Employee', 'Salary']
return result_dfExplanation
- Merge DataFrames:
pd.merge(employee, department, left_on='departmentId', right_on='id', how='inner'): This merges theemployeeanddepartmentDataFrames based on thedepartmentIdfrom theemployeetable and theidfrom thedepartmenttable.- The merged DataFrame will have columns like
id_x,name_x(fromemployee),salary,departmentId,id_y, andname_y(fromdepartment).
- Finding Maximum Salaries:
max_salaries = merged_df.groupby('departmentId')['salary'].max().reset_index(): This groups bydepartmentIdand finds the maximum salary for each department.
- Filtering Employees with Maximum Salaries:
highest_salary_employees = pd.merge(merged_df, max_salaries, on=['departmentId', 'salary'], how='inner'): This merges the maximum salary data back with the original merged DataFrame to keep only those employees who have the maximum salary in their respective departments.
- Selecting and Renaming Columns:
result_df = highest_salary_employees[['name_y', 'name_x', 'salary']]: Select the relevant columns (name_yfor department name,name_xfor employee name, andsalary).result_df.columns = ['Department', 'Employee', 'Salary']: Rename the columns to the desired output format.
Solution in MySQL
SQL
-- Step 1: Find the maximum salary in each department
SELECT d.name AS Department, e.name AS Employee, e.salary AS Salary
FROM Employee e
JOIN Department d ON e.departmentId = d.id
WHERE e.salary = (
SELECT MAX(e2.salary)
FROM Employee e2
WHERE e2.departmentId = e.departmentId
);Solution in PostgreSQL
SQL
-- Step 1: Select the required data
SELECT d.name AS Department, e.name AS Employee, e.salary AS Salary
FROM Employee e
JOIN Department d ON e.departmentId = d.id
-- Step 2: Filter employees with the maximum salary in their department
WHERE e.salary = (
SELECT MAX(e2.salary)
FROM Employee e2
WHERE e2.departmentId = e.departmentId
);