Problem Description –
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.
Problem Link – Group Employees
Difficulty Level – Medium
SELECT DISTINCT *, DENSE_RANK() OVER(ORDER BY salary) as team_id FROM ( SELECT t1.employee_id, t1.name, t1.salary FROM employees as t1, employees as t2 WHERE t1.employee_id != t2.employee_id AND t1.salary = t2.salary ORDER BY 1 ) x ORDER BY team_id, employee_id