Department Top Three Salaries | LeetCode | MSSQL

The Problem

We are given two tables: Employee and Department. The Employee table has the following structure:

where 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.

The Department table is structured as follows:

where 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.

The Solution

We'll examine two SQL solutions to this problem, each leveraging a different SQL construct to solve the problem.

Source Code 1

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.

Source Code 2

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.

Conclusion

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.

ranggakd - Link in Bio & Creator Tools | Beacons

@ranggakd | center details summary summary Oh hello there I m a an Programmer AI Tech Writer Data Practitioner Statistics Math Addict Open Source Contributor Quantum Computing Enthusiast details center.

beacons.ai