We are given two tables:
Employee table has the following structure:
id is the primary key and
departmentId is a foreign key referencing the
Department table. Each row of this table indicates the ID, name, salary, and department ID of an employee.
Department table is structured as follows:
id is the primary key. Each row represents the ID and name of a department.
Our task is to write an SQL query to find the employees who are high earners in their respective departments. A high earner is an employee who has a salary in the top three unique salaries for their department.
We'll examine two SQL solutions to this problem, each leveraging a different SQL construct to solve the problem.
The first code uses a subquery in the
WHERE clause to filter the rows. It counts the distinct salaries greater than the current employee's salary in the same department. If the count is less than 3, the current employee is a top earner:
SELECT d.name AS Department, e.name AS Employee, e.salary AS Salary FROM Employee e INNER JOIN Department d ON e.departmentId = d.id WHERE ( SELECT COUNT(DISTINCT e2.salary) FROM Employee e2 WHERE e2.departmentId = e.departmentId AND e2.salary > e.salary ) < 3
This solution has a runtime of 787ms, beating 63.22% of other solutions. Although this solution works, it can be slow due to the subquery for each employee.
The second solution uses the
DENSE_RANK() function, which provides a ranking within your ordered partition. It gives the same rank to the rows having the same values. The
WITH clause creates a temporary result set (named
rank in this case):
WITH rank AS ( SELECT d.name AS Department, e.name AS Employee, e.salary AS Salary, DENSE_RANK() OVER (PARTITION BY d.name ORDER BY e.salary DESC) AS salary_ranking FROM Employee e JOIN Department d ON e.departmentId = d.id ) SELECT Department, Employee, Salary FROM rank WHERE salary_ranking <= 3
This solution, with a runtime of 629ms, beats 96.48% of other solutions. It performs better due to the use of
DENSE_RANK(), effectively avoiding multiple subquery executions.
While both solutions successfully solve the problem, the second solution using
DENSE_RANK() performs better due to avoiding multiple subquery executions. However, the best choice depends on the specific requirements and constraints of your database or problem scenario.
You can find the original problem at LeetCode.
For more insightful solutions and tech-related content, feel free to connect with me on my Beacons page.