171 ) 12 . show team size of each employee in the query result

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

12 .  show team  size of  each employee in the query result 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 

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      |

---------------------------------------------------------------------------------------------------

Method 1: Using a Window Function (Recommended & Cleanest)

SQL

SELECT 
    employee_id, 
    COUNT(*) OVER (PARTITION BY team_id) AS team_size
FROM 
    Employee;

Method 2: Using a Subquery (Without Window Functions)

SQL

SELECT 
    e1.employee_id,
    (
        SELECT COUNT(*) 
        FROM Employee e2 
        WHERE e2.team_id = e1.team_id
    ) AS team_size
FROM 
    Employee e1;