Group Employees of the Same Salary Problem


Description

LeetCode Problem 1875.

Table: Employees

1
2
3
4
5
6
7
8
9
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| name        | varchar |
| salary      | int     |
+-------------+---------+
employee_id is the primary key for this table.
Each row of this table indicates the employee ID, employee name, and salary.

A company wants to divide the employees into teams such that all the members on each team have the same salary. The teams should follow these criteria:

  • Each team should consist of at least two employees.
  • All the employees on a team should have the same salary.
  • All the employees of the same salary should be assigned to the same team.
  • If the salary of an employee is unique, we do not assign this employee to any team.
  • A team’s ID is assigned based on the rank of the team’s salary relative to the other teams’ salaries, where the team with the lowest salary has team_id = 1. Note that the salaries for employees not on a team are not included in this ranking.

Write an SQL query to get the team_id of each employee that is in a team.

Return the result table ordered by team_id in ascending order. In case of a tie, order it by employee_id in ascending order.

The query result format is in the following example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Employees table:
+-------------+---------+--------+
| employee_id | name    | salary |
+-------------+---------+--------+
| 2           | Meir    | 3000   |
| 3           | Michael | 3000   |
| 7           | Addilyn | 7400   |
| 8           | Juan    | 6100   |
| 9           | Kannon  | 7400   |
+-------------+---------+--------+

Result table:
+-------------+---------+--------+---------+
| employee_id | name    | salary | team_id |
+-------------+---------+--------+---------+
| 2           | Meir    | 3000   | 1       |
| 3           | Michael | 3000   | 1       |
| 7           | Addilyn | 7400   | 2       |
| 9           | Kannon  | 7400   | 2       |
+-------------+---------+--------+---------+

Meir (employee_id=2) and Michael (employee_id=3) are in the same team because they have the same salary of 3000.
Addilyn (employee_id=7) and Kannon (employee_id=9) are in the same team because they have the same salary of 7400.
Juan (employee_id=8) is not included in any team because their salary of 6100 is unique (i.e. no other employee has the same salary).
The team IDs are assigned as follows (based on salary ranking, lowest first):
- team_id=1: Meir and Michael, salary of 3000
- team_id=2: Addilyn and Kannon, salary of 7400
Juan's salary of 6100 is not included in the ranking because they are not on a team.


MySQL Solution

1
2
3
4
5
6
7
8
select *, dense_rank() over(order by salary) team_id
from Employees
where salary in 
	(select salary
	from Employees
	group by salary
	having count(*) > 1)
order by team_id, employee_id




Related Posts

Maximum Transaction Each Day Problem

LeetCode 1831. Write an SQL query to report the IDs...

League Statistics Problem

LeetCode 1841. Write an SQL query to report the statistics...

Group Employees of the Same Salary Problem

LeetCode 1875. Write an SQL query to report the IDs...

Find Customers With Positive Revenue this Year Problem

LeetCode 1821. Write an SQL query to report the customers...

Convert Date Format Problem

LeetCode 1853. Write an SQL query to convert each date...

Calculate Special Bonus Problem

LeetCode 1873. Write an SQL query to calculate the bonus...