Problem

Table: Employee

1
2
3
4
5
6
7
8
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+

id is the primary key column for this table. departmentId is a foreign key of the ID from the Department table. Each row of this table indicates the ID, name, and salary of an employee. It also contains the ID of their department.

Table: Department

1
2
3
4
5
6
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+

id is the primary key column for this table. It is guaranteed that department name is not NULL. Each row of this table indicates the ID of a department and its name.

Write an SQL query to find employees who have the highest salary in each of the departments.

Return the result table in any order.

The query result format is in the following example.

Examples

Example 1:

Input: Employee table:

1
2
3
4
5
6
7
8
9
+----+-------+--------+--------------+
| 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:

1
2
3
4
5
6
+----+-------+
| id | name  |
+----+-------+
| 1  | IT    |
| 2  | Sales |
+----+-------+

Output:

1
2
3
4
5
6
7
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
| IT         | Max      | 90000  |
+------------+----------+--------+

Explanation: Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.

Solution

Method 1 - Join and Subquery

Code

1
2
3
4
SELECT d.name AS Department ,e.name AS Employee, e.salary
FROM Department d JOIN Employee e ON e.departmentId=d.id 
WHERE(e.departmentId, e.salary) IN
(SELECT departmentId,MAX(salary) FROM Employee GROUP BY departmentId) ;
1
2
    if employee.empty or department.empty:
        return pd.DataFrame(columns=['Department','Employee', 'Salary'])
1
    merged_df = employee.merge(department, left_on='departmentId', right_on='id', suffixes=('_employee', '_department'))
1
    highest_salary_df = merged_df.groupby('departmentId').apply(lambda x: x[x['salary'] == x['salary'].max()])
1
    highest_salary_df = highest_salary_df.reset_index(drop=True)
1
    result_df = highest_salary_df[['name_department', 'name_employee', 'salary']]
1
    result_df.columns = ['Department','Employee', 'Salary']
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
import pandas as pd

def department_highest_salary(employee: pd.DataFrame, department: pd.DataFrame) -> pd.DataFrame:
    if employee.empty or department.empty:
        return pd.DataFrame(columns=['Department','Employee', 'Salary'])
    
    merged_df = employee.merge(department, left_on='departmentId', right_on='id', suffixes=('_employee', '_department'))
    
    highest_salary_df = merged_df.groupby('departmentId').apply(lambda x: x[x['salary'] == x['salary'].max()])
    

    highest_salary_df = highest_salary_df.reset_index(drop=True)
    

    result_df = highest_salary_df[['name_department', 'name_employee', 'salary']]
    
    result_df.columns = ['Department','Employee', 'Salary']
    
    return result_df