185. Department Top Three Salaries

Department Top Three Salaries

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', '85000', '1')

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

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

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

    insert into Employee
        (id, name, salary, departmentId) 
        values ('5', 'Janet', '69000', '1')

    insert into Employee
        (id, name, salary, departmentId) 
        values ('6', 'Randy', '85000', '1')

    insert into Employee
        (id, name, salary, departmentId) 
        values ('7', 'Will', '70000', '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.

 

A company's executives are interested in seeing who earns the most money in each of the company's departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department.

Write an SQL query to find the employees who are high earners 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   | 85000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
| 7  | Will  | 70000  | 1            |
+----+-------+--------+--------------+

Department table:
+----+-------+
| id | name  |
+----+-------+
| 1  | IT    |
| 2  | Sales |
+----+-------+

Output: 
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Joe      | 85000  |
| IT         | Randy    | 85000  |
| IT         | Will     | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

Explanation: 
In the IT department:
- Max earns the highest unique salary
- Both Randy and Joe earn the second-highest unique salary
- Will earns the third-highest unique salary

In the Sales department:
- Henry earns the highest salary
- Sam earns the second-highest salary
- There is no third-highest salary as there are only two employees

Approach) Window Function

WITH src AS(
            SELECT d.name       AS Department
                 , e.name       AS Employee
                 , e.salary     AS Salary
                 , DENSE_RANK() OVER(PARTITION BY e.departmentId ORDER BY e.salary DESC) AS rn
              FROM employee   e
                   INNER JOIN
                   department d
                    ON e.departmentId = d.id
           )
SELECT Department, Employee, Salary
  FROM src
 WHERE rn < 4

Approach) SubQuery

SELECT Department,Employee,Salary
FROM
(SELECT
d.name AS Department,
e.name AS Employee,
e.Salary AS Salary,
dense_rank() over(PARTITION BY e.departmentId ORDER BY e.Salary DESC) AS rk
FROM Employee e
LEFT JOIN Department d ON e.departmentId = d.id) a
WHERE rk <= 3

OR

SELECT d.Name Department, e1.Name Employee, e1.Salary
FROM Employee e1 
JOIN Department d
ON e1.DepartmentId = d.Id
WHERE 3 > (SELECT COUNT(DISTINCT(e2.Salary)) 
                  FROM Employee e2 
                  WHERE e2.Salary > e1.Salary 
                  AND e1.DepartmentId = e2.DepartmentId
                  );

Conclusion

That’s all folks! In this post, we solved LeetCode problem #185. Department Top Three Salaries

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!