Welcome to Subscribe On Youtube
184. Department Highest Salary
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 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
+-------------+---------+
| 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 not NULL.
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.
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 | +------------+----------+--------+ Explanation: Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.
Solutions
Solution 1: Equi-Join + Subquery
We can use an equi-join to join the Employee table and the Department table based on Employee.departmentId = Department.id, and then use a subquery to find the highest salary for each department. Finally, we can use a WHERE clause to filter out the employees with the highest salary in each department.
Solution 2: Equi-Join + Window Function
We can use an equi-join to join the Employee table and the Department table based on Employee.departmentId = Department.id, and then use the window function rank(), which assigns a rank to each employee in each department based on their salary. Finally, we can select the rows with a rank of $1$ for each department.
-
# Write your MySQL query statement below SELECT Department.name AS 'Department', Employee.name AS 'Employee', Employee.Salary FROM Employee JOIN -- default is inner join Department ON Employee.DepartmentId = Department.Id WHERE (Employee.DepartmentId , Employee.Salary) IN -- so that if tie, both will appear in result ( SELECT DepartmentId, MAX(Salary) FROM Employee GROUP BY DepartmentId ) ; -
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