This is hard LeetCode question, and if you can solve it, it means you are among the top 10% of people who possess a deep understanding of SQL and have hands-on experience—I am one of them. To become proficient at solving such questions, it’s essential to have a strong grasp of SQL as a whole.
I’d like to emphasize that mastering SQL doesn’t necessarily require a significant amount of time. Instead, it involves the ability to recognize patterns in questions, enabling you to tackle similar problems efficiently.
Topic Understanding For Question
- common table expression (CTE)
- Joins
- alias (as)
- windows query
- order by (ASC, DESC)
- in operator
The Question
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.
The 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
The Solution
with cte as(select id, salary, name, departmentId,
dense_rank() over(partition by departmentId order by salary desc) as Ranking
from employee)
select department.name as Department, cte.name as Employee, Salary
from cte inner join department
on cte.departmentId = department.id
where Ranking in (1, 2, 3)
Explanation:
In This SQL query we used a Common Table Expression (CTE) to assign ranks to employees’ salaries within their respective departments. These ranks are assigned in descending order, and employees with the same salary are given the same rank, achieved through the use of the dense_rank() window function.
In the main query, we perform a join between the CTE and the department table, followed by filtering the records using the IN operator.
Conclusion
I hope you understood this question’s answer very well. Guys, don’t worry; these types of questions may take some time to grasp, but they help us expand our knowledge boundaries. Friends, please don’t forget to visit this site again. I am always here for you. Feel free to comment with your queries regarding SQL, Python, Power BI, AWS data pipelining, exploratory data analysis (EDA), or PySpark.