Group Employees of the Same Salary - Problem

A company wants to divide employees into teams where all members have the same salary. The teams must follow these criteria:

  • Each team must have at least 2 employees
  • All employees on a team must have identical salaries
  • All employees with the same salary must be on the same team
  • Employees with unique salaries are not assigned to any team

Team IDs are assigned based on salary rank, where the team with the lowest salary gets team_id = 1.

Write a SQL query to return the team_id for each employee that belongs to a team, ordered by team_id ascending, then by employee_id ascending.

Table Schema

Employees
Column Name Type Description
employee_id PK int Unique employee identifier
name varchar Employee name
salary int Employee salary
Primary Key: employee_id
Note: Each row represents one employee with their salary information

Input & Output

Example 1 — Basic Team Formation
Input Table:
employee_id name salary
1 Alice 100
2 Bob 200
3 Charlie 100
4 David 300
Output:
employee_id name team_id
1 Alice 1
3 Charlie 1
💡 Note:

Alice and Charlie both earn $100, so they form team 1 (lowest salary). Bob ($200) and David ($300) have unique salaries and don't get assigned to teams.
Example 2 — Multiple Teams
Input Table:
employee_id name salary
1 Alice 100
2 Bob 200
3 Charlie 100
4 David 200
5 Eve 300
Output:
employee_id name team_id
1 Alice 1
3 Charlie 1
2 Bob 2
4 David 2
💡 Note:

Alice and Charlie ($100) form team 1. Bob and David ($200) form team 2. Eve ($300) has unique salary and is not assigned to a team. Teams are ranked by salary: 100 → team 1, 200 → team 2.
Example 3 — All Unique Salaries
Input Table:
employee_id name salary
1 Alice 100
2 Bob 200
3 Charlie 300
Output:
employee_id name team_id
💡 Note:

All employees have unique salaries, so no teams can be formed. The result is empty since teams require at least 2 employees with the same salary.

Constraints

  • 1 ≤ employee_id ≤ 1000
  • 1 ≤ name.length ≤ 20
  • 1 ≤ salary ≤ 100000
  • 1 ≤ Employees.length ≤ 500

Visualization

Tap to expand
Group Employees of the Same Salary INPUT: Employees Table emp_id name salary 1 Alice 5000 2 Bob 5000 3 Carol 6000 4 David 6000 5 Eve 6000 6 Frank 7000 Salary Groups: $5000 2 emps $6000 3 emps $7000 1 emp Frank excluded (unique salary) ALGORITHM STEPS 1 Count Salaries GROUP BY salary, COUNT(*) Keep only count >= 2 2 Rank Salaries DENSE_RANK() by salary ASC Lowest salary = team 1 $5000 --> team_id = 1 $6000 --> team_id = 2 $7000 --> excluded 3 Join Results Match employees to teams via salary field 4 Order Output ORDER BY team_id, employee_id FINAL RESULT emp_id name team 1 Alice 1 2 Bob 1 3 Carol 2 4 David 2 5 Eve 2 Team Formation: Team 1 Alice Bob $5000 Team 2 Carol David, Eve $6000 OK - 5 employees assigned Frank excluded (unique) Key Insight: Use DENSE_RANK() to assign team IDs based on salary order. Filter salaries with COUNT >= 2 to ensure each team has at least 2 members. Join back to get employee details with team assignments. TutorialsPoint - Group Employees of the Same Salary | Optimal Solution

Related Problems

Asked in
Amazon 23 Google 18 Microsoft 15
28.5K Views
Medium Frequency
~12 min Avg. Time
845 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen