SQL MOSAIC #1303. Find the Team Size

Date: 7/6/2022

Write an SQL query to find the team size of each of the employees.

Return result table in any order.

Thinking Pathway:

1. I just wanted to post this question to add a second approach.

Solution(s):

Solution1:

WITH T AS (
SELECT team_id, COUNT(employee_id) AS team_size
FROM Employee
GROUP BY team_id)

SELECT employee_id, team_size
FROM Employee e LEFT JOIN T on e.team_id = T.team_id

Solution2:

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

Explanation:

• You can think of “partition by” as the pivot function in Excel. When you partition by, you will group a specific field.
• Then, you can add aggregation function OVER the partition by function, such as SUM(team_size) OVER (PARTITION BY xxx), COUNT(*) OVER (PARTITION BY xxx).