Department Top Three Salaries | LeetCode | MSSQL
Tags: mssql, leetcode, beginners, codenewbie
Published: 2023-05-19 15:39:14

## 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:

```sql
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.

![first result](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dqbkw0z92srybgmovimf.png)

### 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):

```sql
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.

![second result](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xolssednx9rh6vhco41t.png)

## 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](https://leetcode.com/problems/department-top-three-salaries/description/).

For more insightful solutions and tech-related content, feel free to connect with me on my [Beacons page](https://beacons.ai/ranggakd).

{% embed https://beacons.ai/ranggakd %}