+---------------+---------+
|Column Name |Type|+---------------+---------+
| employee_id | int || team_id | int |+---------------+---------+
employee_id is the primarykey (columnwithuniquevalues) for this table.
Eachrowof this tablecontains the ID ofeach employee and their respective team.
Write a solution to find the team size of each of the employees.
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 4is part of a team with team_id =7.Employees with Id 5,6 are part of a team with team_id =9.
SELECT em.employee_id,
sub.total team_size
FROM Employee em
JOIN (
SELECT team_id, COUNT(*) total
FROM Employee
GROUPby team_id) sub ON sub.team_id = em.team_id;
1
2
3
SELECT employee_id,
COUNT(team_id) OVER(PARTITION BY team_id) AS team_size
FROM Employee