SQL Interview Questions – Group Employees of the Same Salary

Spread the love

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 –

    DENSE_RANK() OVER(ORDER BY salary) as team_id
FROM employees as t1, employees as t2
WHERE t1.employee_id != t2.employee_id AND t1.salary = t2.salary
    ) x
ORDER BY team_id, employee_id

Rating: 1 out of 5.

Leave a Reply