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 by Salary OverviewInput: All Employeesemployee_idnamesalary1Alice1002Bob2003Charlie1004David300GROUP BYsalarySalary 100: 2 employees ✓Salary 200: 1 employee ✗Salary 300: 1 employee ✗Filter: count >= 2Output: Teams Onlyemployee_idnameteam_id1Alice13Charlie1Team ID = DENSE_RANK() of salary (100 → team_id 1)
Understanding the Visualization
1
Group
Count employees per salary
2
Filter
Keep salaries with 2+ employees
3
Rank
Assign team_id by salary order
Key Takeaway
🎯 Key Insight: Use window functions to count duplicates and rank by salary for efficient team assignment

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