185 Department Top Three Salaries

Description

Table: Employee

• id is the primary key (column with unique values) for this table.

• departmentId is a foreign key (reference column) of the ID from the Department table.

• Each row of this table indicates the ID, name, and salary of an employee. It also contains the ID of their department.

Table: Department

• id is the primary key (column with unique values) for this table.

• Each row of this table indicates the ID of a department and its name.

Problem Statement

A company’s executives are interested in seeing who earns the most money in each of the company’s departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department.

Write a solution to find the employees who are high earners 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: In the IT department:

• Max earns the highest unique salary
• Both Randy and Joe earn the second-highest unique salary
• Will earns the third-highest unique salary

In the Sales department:

• Henry earns the highest salary
• Sam earns the second-highest salary
• There is no third-highest salary as there are only two employees

Solution

The problem states that we need to find employees who are high earners in each of the departments. If there are two employees with same salary, they should be included in the result. This is where we can use DENSE_RANK() window function. Although RANK() function can return the rank of employees based on their salary, it may skip the consecutive ranks if we have two employees with same salary.

The problem can be divided into following parts:

1. For each employee, find their department.
2. For each employee department record, create a partition based on their department and sort them in descending order of their salary. Find the top three earners in each department using DENSE_RANK() window function.

 1WITH cte AS (
 2    SELECT e.name Employee, e.salary Salary, d.name Department,
 3        DENSE_RANK() OVER (PARTITION BY d.id ORDER BY e.salary DESC) rnk
 4        FROM Employee e
 5        JOIN Department d
 6        ON e.departmentId = d.id
 7) SELECT Department, Employee, Salary
 8    FROM cte
 9    WHERE rnk <= 3
10    ORDER BY rnk;