00:00:00

Description

Editorial

Salary Band Team Assignment

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

employees =

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

Loading problem...

101

00:00:00

Description

Editorial

Salary Band Team Assignment

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

employees =

Salary Band Team Assignment

Hints

Salary Band Team Assignment

Hints