Department Top Three Salaries: Leetcode SQL
In today’s data-driven world, organizations strive to gain insights from their datasets to make informed decisions. One common analytical task is identifying top performers or high earners within various departments of a company. In this blog post, we’ll explore how to determine the highest-earning employees in each department using SQL. We’ll walk through the problem statement, understand the data structure, and implement a step-by-step solution.
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 column) 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.
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 a solution to find the employees who are high earners in each of the departments. Return the result table in any order.
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 |
+----+-------+--------+--------------+
Each row of this table indicates the ID, name, and salary of an employee.
It also contains the ID of their department.
Department table:
+----+-------+
| id | name |
+----+-------+
| 1 | IT |
| 2 | Sales |
+----+-------+
Each row of this table indicates the ID of a department and its name.
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
Constraints: There are no employees with the exact same name, salary
and department.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')MySQL, PostgreSQL, and MS SQL Server Solution:
-- Step 1: Create a Common Table Expression (CTE) named "cte"
WITH cte AS (
-- Select department name, employee name, salary, and rank
SELECT
d.name AS Department, -- Get the name of the department
e.name AS Employee, -- Get the name of the employee
salary AS Salary, -- Get the salary of the employee
-- Use DENSE_RANK() to rank employees' salaries within each department
-- The ranking starts over for each department due to PARTITION BY d.name
-- Salaries are ordered in descending order, so the highest salary gets rank 1
DENSE_RANK() OVER (
PARTITION BY d.name -- Reset ranking for each department
ORDER BY salary DESC -- Order salaries in descending order
) AS rnk -- Assign rank to the column named "rnk"
FROM
Employee AS e -- Employee table (contains employee details)
LEFT JOIN
Department AS d -- Department table (contains department details)
ON
e.departmentId = d.id -- Join the tables using departmentId to connect employees to their departments
)
-- Step 2: Use the result of the CTE in the main query
SELECT
Department, -- Retrieve the department name
Employee, -- Retrieve the employee name
Salary -- Retrieve the salary
FROM
cte -- Use the temporary result set created by the CTE
WHERE
rnk IN (1, 2, 3); -- Filter to include only employees ranked 1st, 2nd, or 3rd in salary for each departmentHappy analyzing!
References: [1] Leetcode Source: department-top-three-salaries
Follow me on LinkedIn Instagram Medium profile or subscribe to my email list for the latest blogs, and click the clap button to support writing. Feel free to ask questions in the comment section. Don’t forget to share the blog link with your friends or LinkedIn connections.
