184. Department Highest Salary

Department Highest Salary

Problem Description

SQL Schema

    

    Create table If Not Exists Employee
    (id int, name varchar(255), salary int, departmentId int)

    Create table If Not Exists Department
        (id int, name varchar(255))

    Truncate table Employee

    insert into Employee
        (id, name, salary, departmentId) 
        values ('1', 'Joe', '70000', '1')

    insert into Employee
        (id, name, salary, departmentId) 
        values ('2', 'Jim', '90000', '1')

    insert into Employee
        (id, name, salary, departmentId) 
        values ('3', 'Henry', '80000', '2')

    insert into Employee
        (id, name, salary, departmentId) 
        values ('4', 'Sam', '60000', '2')

    insert into Employee
        (id, name, salary, departmentId) 
        values ('5', 'Max', '90000', '1')

    Truncate table Department

    insert into Department (id, name) values ('1', 'IT')

    insert into Department (id, name) values ('2', 'Sales')

Table: Employee

+--------------+---------+
| 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

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+

id is the primary key column for this table.
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.

 

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.

Approach) Using CTE

WITH cte 
AS(
    SELECT 
        departmentId, 
        salary AS Salary,
        name AS Employee,
        DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS 'salary_rank'
    FROM Employee)

SELECT d.name AS Department, e.Employee, e.Salary
FROM Department d
LEFT JOIN cte e
ON d.id = e.departmentId
WHERE salary_rank = 1

Approach) Using Subquery

SELECT d.name AS Department, e.Employee, e.Salary
FROM Department d
LEFT JOIN (
        SELECT 
            departmentId, 
            salary AS Salary,
            name AS Employee,
            DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS 'salary_rank'
        FROM Employee) e
ON d.id = e.departmentId
WHERE salary_rank = 1

Both are similar approaches.

NOTE:

It doesn't make a difference whether to use DENSE_RANK() OR RANK() here, since the question had asked about just the highest salary. But if it was about Nth Highest salary, then use DENSE_RANK(), so as to not leave gaps in ranking and just change WHERE salary_rank = 1 to WHERE salary_rank = N

Conclusion

That’s all folks! In this post, we solved LeetCode problem #184. Department Highest Salary

I hope you have enjoyed this post. Feel free to share your thoughts on this.

You can find the complete source code on my GitHub repository. If you like what you learn. feel free to fork 🔪 and star ⭐ it.

In this blog, I have tried to solve leetcode questions & present the most important points to consider when improving Data structure and logic,  feel free to add, edit, comment, or ask. For more information please reach me here
Happy coding!