Find the Team Size - Solution & Explanation
Problem Statement
Table: Employee
+---------------+---------+ | Column Name | Type | +---------------+---------+ | employee_id | int | | team_id | int | +---------------+---------+ employee_id is the primary key (column with unique values) for this table. Each row of this table contains the ID of each employee and their respective team.
Write a solution to find the team size of each of the employees.
Return the result table in any order.
The result format is in the following example.
Example 1:
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 4 is part of a team with team_id = 7. Employees with Id 5,6 are part of a team with team_id = 9.
Approach Overview
Problem Overview: You are given an Employee table containing employee_id and team_id. For every employee, return the number of employees in the same team. The output must include each employee’s ID along with their team size.
Approach 1: Group By + Equi-Join (O(n) time, O(n) space)
This approach first calculates the size of each team using a GROUP BY aggregation on team_id. The query computes COUNT(*) for every team and stores the result as a derived table. You then join this aggregated result back to the original Employee table using an equi-join on team_id. Each employee row picks up the precomputed team size from the grouped result.
The key insight: aggregation should happen once per team, not once per employee. By separating the counting step from the final output, the database engine performs a single scan for grouping and a lightweight join afterward. This pattern is common in SQL problems where row-level output depends on group-level metrics.
This is typically the most efficient and readable solution. Time complexity is O(n) for scanning and grouping the table, and space complexity is O(n) for storing the aggregated team counts.
Approach 2: Self Left Join Counting (O(n²) worst-case time, O(1) extra space)
A second method joins the Employee table with itself using LEFT JOIN on matching team_id. For each employee row, the join pairs it with every other employee in the same team. Counting the joined rows gives the team size.
This solution relies on a classic self-join pattern: match each row against all rows sharing the same grouping key. After joining, apply COUNT() with a GROUP BY employee_id to collapse duplicates and compute the team size.
The downside is scalability. If a team contains many members, the join generates many intermediate rows. In the worst case, this behaves like O(n²) when a large portion of employees belong to the same team. Still, the query is straightforward and demonstrates how relational joins can replace aggregation logic. This technique appears frequently in database interview questions involving joins.
Recommended for interviews: The Group By + Equi-Join solution is what most interviewers expect. It shows you understand how to separate aggregation from row-level queries and how to reuse grouped results efficiently. The self-join version works but signals weaker query optimization instincts. Demonstrating both approaches shows solid understanding of SQL query planning and relational operations.
Approach 1: Group By + Equi-Join
We can first count the number of people in each team and record it in the T table. Then, we can use an equi-join to join the Employee table and the T table based on team_id, and obtain the total number of people in each team.
Code
MySQL
Approach 2: Left Join
We can also use a left join to join the Employee table with itself based on team_id, and then group by employee_id to count the total number of people in each team that the employee belongs to.
Code
MySQL
Complexity Comparison
| Approach | Complexity |
|---|---|
| Group By + Equi-Join | — |
| Left Join | — |
Detailed Complexity Analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Group By + Equi-Join | O(n) | O(n) | Best general solution. Efficient aggregation per team and easy to read. |
| Self Left Join Counting | O(n²) worst case | O(1) | Useful to demonstrate join logic or when practicing relational self-joins. |
Video Solution
LeetCode 1303 Interview SQL Question with Detailed Explanation | Practice SQL | Window Functions • Everyday Data Science • 9,285 views views
Watch 2 more video solutions →Frequently Asked Questions
Is Find the Team Size easy or hard?
Find the Team Size Python/Java solution
How to solve Find the Team Size in O(n)?
What is the best approach for Find the Team Size?
Is Find the Team Size asked at Google/Amazon/Meta?
What data structure is used in Find the Team Size?
What is the time complexity of Find the Team Size?
Ready to solve this problem?
Practice Find the Team Size with our built-in code editor and test cases.
Practice on FleetCode