Find the Team Size 17-10-22

link -- https://www.codingninjas.com/codestudio/problems/find-the-team-size_2117109?topList=top-100-sql-problems&leftPanelTab=0

Problem Statement

 Table: Employee

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| employee_id   | int     |
| team_id       | int     |
+---------------+---------+
employee_id is the primary key for this table.
Each row of this table contains the ID of each employee and their respective team.
Write an SQL query to find the team size of each of the employees.

Return result table in any order.

 The query result format is in the following example:

 Employee Table:
 +-------------+------------+
 | employee_id | team_id    |
 +-------------+------------+
 |     1       |     8      |
 |     2       |     8      |
 |     3       |     8      |
 |     4       |     7      |
 |     5       |     9      |
 |     6       |     9      |
 +-------------+------------+

 Result table:
+-------------+------------+
| employee_id | team_size  |
+-------------+------------+
|     1       |     3      |
|     2       |     3      |
|     3       |     3      |
|     4       |     1      |
|     5       |     2      |
|     6       |     2      |
+-------------+------------+
Employees with Id 1,2,3 are part of a team with team_id = 8.
Employees 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.


------------------ solution 1 ----------------------


with newt as (select team_id , count(team_id) cnt from employee
group by 1)

select t1.employee_id,t2.cnt team_size from 
employee t1 
join newt t2 
on t1.team_id=t2.team_id



----------------- solution 2 --------------------


SELECT e.employee_id, team_size
FROM Employee e 
 JOIN (SELECT team_id, COUNT(*) AS team_size
     FROM Employee 
     GROUP BY team_id) t
ON e.team_id = t.team_id;