find-the-team-size.sql

/*

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.

*/


# V0
select t1.employee_id, t2.team_size
from Employee as t1
inner join (select team_id, count(1) as team_size
    from Employee
    group by team_id) as t2
on t1.team_id = t2.team_id

# V1
# https://code.dennyzhang.com/find-the-team-size
select t1.employee_id, t2.team_size
from Employee as t1
inner join (select team_id, count(1) as team_size
    from Employee
    group by team_id) as t2
on t1.team_id = t2.team_id

# V2
# Time:  O(n)
# Space: O(n)
SELECT employee_id, 
       team_size 
FROM   employee AS e 
       LEFT JOIN (SELECT team_id, 
                         Count(1) AS team_size 
                  FROM   employee 
                  GROUP  BY team_id) AS teams 
              ON e.team_id = teams.team_id