## DEV Community

Retiago Drago

Posted on • Updated on

# Department Top Three Salaries | LeetCode | MSSQL

## The Problem

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

Column Name Type
id int
name varchar
salary int
departmentId int

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:

Column Name Type
id int
name varchar

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