1303. Find the Team Size π
Description
Table: Employee
+---------------+---------+ | Column Name | Type | +---------------+---------+ | employee_id | int | | team_id | int | +---------------+---------+ employee_id is the primary key (column with unique values) for this table. Each row of this table contains the ID of each employee and their respective team.
Write a solution to find the team size of each of the employees.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Employee Table: +-------------+------------+ | employee_id | team_id | +-------------+------------+ | 1 | 8 | | 2 | 8 | | 3 | 8 | | 4 | 7 | | 5 | 9 | | 6 | 9 | +-------------+------------+ Output: +-------------+------------+ | employee_id | team_size | +-------------+------------+ | 1 | 3 | | 2 | 3 | | 3 | 3 | | 4 | 1 | | 5 | 2 | | 6 | 2 | +-------------+------------+ Explanation: Employees with Id 1,2,3 are part of a team with team_id = 8. Employee with Id 4 is part of a team with team_id = 7. Employees with Id 5,6 are part of a team with team_id = 9.
Solutions
Solution 1: Group By + Equi-Join
We can first count the number of people in each team and record it in the T
table. Then, we can use an equi-join to join the Employee
table and the T
table based on team_id
, and obtain the total number of people in each team.
1 2 3 4 5 6 7 8 9 10 11 |
|
Solution 2: Left Join
We can also use a left join to join the Employee
table with itself based on team_id
, and then group by employee_id
to count the total number of people in each team that the employee belongs to.
1 2 3 4 5 6 |
|