Team Headcount by Member

Easy

A workforce directory stores each employee and the team they belong to. Table: employee employee_id: unique identifier for each employee team_id: identifier of the team the employee belongs to Task: For every employee row, return the employee_id and the number of employees in that same team. Output requirements: Return exactly two columns named employee_id and team_size. Include one output row per employee. Result can be returned in any order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

employee:
| employee_id | team_id |
|-------------|---------|
| 1           | 8       |
| 2           | 8       |
| 3           | 8       |
| 4           | 7       |
| 5           | 9       |
| 6           | 9       |

Output

[
  {"employee_id":1,"team_size":3},
  {"employee_id":2,"team_size":3},
  {"employee_id":3,"team_size":3},
  {"employee_id":4,"team_size":1},
  {"employee_id":5,"team_size":2},
  {"employee_id":6,"team_size":2}
]

Explanation

Team 8 has three members (1,2,3), team 7 has one member (4), and team 9 has two members (5,6).

Example 2

Input

employee:
| employee_id | team_id |
|-------------|---------|
| 10          | 50      |
| 20          | 50      |
| 30          | 50      |
| 40          | 50      |

Output

[
  {"employee_id":10,"team_size":4},
  {"employee_id":20,"team_size":4},
  {"employee_id":30,"team_size":4},
  {"employee_id":40,"team_size":4}
]

Explanation

All employees are in the same team, so each employee gets team_size = 4.

Example 3

Input

employee:
| employee_id | team_id |
|-------------|---------|
| 100         | 1       |
| 200         | 2       |
| 300         | 3       |

Output

[
  {"employee_id":100,"team_size":1},
  {"employee_id":200,"team_size":1},
  {"employee_id":300,"team_size":1}
]

Explanation

Each employee is alone in their team, so every row has team_size = 1.

Constraints

0 <= number of rows in employee <= 200000
1 <= employee_id, team_id <= 1000000000
employee_id is unique
Every employee belongs to exactly one team
Return one row per employee
Return exactly two columns: employee_id, team_size
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns [employee_id, team_size]

employee: | employee_id | team_id | |-------------|---------| | 1 | 8 | | 2 | 8 | | 3 | 8 | | 4 | 7 | | 5 | 9 | | 6 | 9 |

[ {"employee_id":1,"team_size":3}, {"employee_id":2,"team_size":3}, {"employee_id":3,"team_size":3}, {"employee_id":4,"team_size":1}, {"employee_id":5,"team_size":2}, {"employee_id":6,"team_size":2} ]