Salary Band Team Assignment

Medium

A compensation analytics system stores employee records and needs to create pay-based cohorts for benchmarking. Table: employees employee_id: unique employee identifier name: employee name salary: annual compensation amount Team formation policy: A salary value forms a team only when at least two employees share that salary. All employees with that qualifying salary must be included in the same team. Employees with unique salaries are excluded from all teams. Team ID policy: Consider only qualifying salaries. Rank those salaries from low to high. Assign team_id starting at 1 in that order. Task: Return every employee who belongs to a team, together with the computed team_id. Output requirements: Return columns in this exact order: employee_id name salary team_id Sort by team_id ascending, then employee_id ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

employees:
| employee_id | name    | salary |
|-------------|---------|--------|
| 2           | Meir    | 3000   |
| 3           | Michael | 3000   |
| 7           | Addilyn | 7400   |
| 8           | Juan    | 6100   |
| 9           | Kannon  | 7400   |

Output

[
  {"employee_id":2,"name":"Meir","salary":3000,"team_id":1},
  {"employee_id":3,"name":"Michael","salary":3000,"team_id":1},
  {"employee_id":7,"name":"Addilyn","salary":7400,"team_id":2},
  {"employee_id":9,"name":"Kannon","salary":7400,"team_id":2}
]

Explanation

Salary 6100 appears once and is excluded. Qualifying salaries are 3000 and 7400, so they receive team IDs 1 and 2 respectively.

Example 2

Input

employees:
| employee_id | name   | salary |
|-------------|--------|--------|
| 10          | Avery  | 5000   |
| 11          | Blair  | 5000   |
| 12          | Casey  | 5000   |
| 13          | Devon  | 9000   |

Output

[
  {"employee_id":10,"name":"Avery","salary":5000,"team_id":1},
  {"employee_id":11,"name":"Blair","salary":5000,"team_id":1},
  {"employee_id":12,"name":"Casey","salary":5000,"team_id":1}
]

Explanation

Only salary 5000 qualifies because it has at least two employees. Salary 9000 is unique and excluded.

Example 3

Input

employees:
| employee_id | name  | salary |
|-------------|-------|--------|
| 101         | Iris  | 4200   |
| 102         | Jonah | 5300   |
| 103         | Lila  | 6100   |

Output

[]

Explanation

Every salary is unique, so no salary team is created.

Constraints

1 <= number of rows in employees <= 200000
1 <= employee_id <= 1000000000
employee_id is unique
1 <= length(name) <= 64
name contains English letters only
0 <= salary <= 1000000000
Return exactly columns: employee_id, name, salary, team_id
Sort by team_id ascending, then employee_id ascending
SQL submissions must follow SQLite 3.27.2 behavior
Pandas submissions must return a DataFrame with required columns

employees: | employee_id | name | salary | |-------------|---------|--------| | 2 | Meir | 3000 | | 3 | Michael | 3000 | | 7 | Addilyn | 7400 | | 8 | Juan | 6100 | | 9 | Kannon | 7400 |

[ {"employee_id":2,"name":"Meir","salary":3000,"team_id":1}, {"employee_id":3,"name":"Michael","salary":3000,"team_id":1}, {"employee_id":7,"name":"Addilyn","salary":7400,"team_id":2}, {"employee_id":9,"name":"Kannon","salary":7400,"team_id":2} ]

employees: | employee_id | name | salary | |-------------|--------|--------| | 10 | Avery | 5000 | | 11 | Blair | 5000 | | 12 | Casey | 5000 | | 13 | Devon | 9000 |