LeetCode 184. Department Highest Salary (SQL Solution)

This is a LeetCode medium-level SQL question. To successfully solve this SQL question, you should have a good understanding of advanced SQL concepts such as window functions, common table expressions (CTE), and various types of joins. Additionally, it’s crucial to practice these types of questions to develop the ability to identify patterns within them. Let’s proceed to tackle this question.

The Question

Table: Employee

Table: Department

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:

Department table:

Output:

Explanation: Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.

---

The Solution

with cte as(select id, name, salary, 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=1

Explanation

Step 1 : First, in the CTE (Common Table Expression) clause, I added a ranking to the salaries based on the departmentId in descending order.

Step 2 : Then, in the main query, I joined two tables: the CTE and the department table. I filtered the records based on the first ranking, ensuring that we obtain all employees who are receiving the highest salaries within their respective departments.

A helpful clue for solving problems like this is first assign ranks using the dense_rank() function. After ranking the data, you can proceed to join and filter records based on that ranking.

Conclusion

Solving problems of this nature requires a clear understanding of SQL topics and their use cases. You should have a good grasp of when to apply specific SQL functions. If you master these concepts, I am confident that you will excel in SQL. Don’t forget to visit this website again for more such problems in the future.