SQL Interview Questions – Group Employees of the Same Salary

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

Solution –

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