185. Department Top Three Salaries

Description of Problem

Table: Employee

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+
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

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
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.

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:
+----+-------+--------+--------------+
| id | name  | salary | departmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 85000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
| 7  | Will  | 70000  | 1            |
+----+-------+--------+--------------+
Department table:
+----+-------+
| id | name  |
+----+-------+
| 1  | IT    |
| 2  | Sales |
+----+-------+
Output: 
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Joe      | 85000  |
| IT         | Randy    | 85000  |
| IT         | Will     | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+
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 1 - Using ROW_NUMBER() and PARTITION BY

Tags: SQL Grouping Joins

-- Write your MySQL query statement below
WITH p AS (
    SELECT 
        ROW_NUMBER() OVER (
            PARTITION BY departmentId 
            ORDER BY salary DESC
        ) AS row_num, 
        salary, 
        departmentId
FROM Employee
GROUP BY departmentId, salary
)
SELECT 
    (SELECT name FROM Department d WHERE d.id = e.departmentId) AS Department,
    e.name AS Employee,
    e.salary AS Salary
FROM Employee e
INNER JOIN p
ON e.departmentId = p.departmentId
AND e.salary = p.salary
WHERE row_num <= 3
;

Solution 2 - Using Subquery

Tags: SQL Subquery

-- Write your MySQL query statement below
SELECT 
    (SELECT d.name FROM Department d WHERE d.id = e1.departmentId) AS Department,
    e1.name AS Employee,
    e1.salary AS Salary
FROM Employee e1
-- The count of "higher salary in the same department" is smaller than 3
WHERE 3 > (
    SELECT COUNT(DISTINCT e2.salary) 
    FROM Employee e2 
    WHERE e1.salary < e2.salary 
    AND e1.departmentId = e2.departmentId
);