Find the Team Size solution and explanation
2 more videos on this problem
LeetCode 1303 Interview SQL Question with Detailed Explanation | Practice SQL | Window FunctionsEveryday Data Science · 7:56 · 9K views
Advanced Tech Interview Questions that Pay Off! - SQL Data Science | Leetcode 1303Everyday Data Science · 3:56 · 741 views
Find the Team Size. Leetcode Problem and Solution #DataDnyan #sql #dataengineeringDataDnyan · 8:22 · 319 views
The problem
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.
# Write your MySQL query statement below
WITH
T AS (
SELECT team_id, COUNT(1) AS team_size
FROM Employee
GROUP BY 1
)
SELECT employee_id, team_size
FROM
Employee
JOIN T USING (team_id);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.
# Write your MySQL query statement below
SELECT e1.employee_id, COUNT(1) AS team_size
FROM
Employee AS e1
LEFT JOIN Employee AS e2 USING (team_id)
GROUP BY 1;Complexity comparison
| Approach | Complexity |
|---|---|
| Group By + Equi-Join | – |
| Left Join | – |
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. |
Frequently asked questions
Is Find the Team Size easy or hard?
Find the Team Size is classified as an Easy database problem on LeetCode with a high acceptance rate around 89%. The challenge mainly checks your familiarity with SQL aggregation and join patterns.
Find the Team Size Python/Java solution
This is a SQL database problem rather than a Python or Java algorithm question. The solution is written as a SQL query using GROUP BY and JOIN operations, typically executed in MySQL or similar relational databases.
How to solve Find the Team Size in O(n)?
First aggregate the Employee table by team_id using COUNT(*) to compute each team's size. Store this result in a subquery or derived table. Join the aggregated result back to the Employee table on team_id so every employee row receives the corresponding team size.
What is the best approach for Find the Team Size?
The most efficient solution uses GROUP BY to compute the number of employees per team and then joins that result back to the Employee table using team_id. This avoids repeated counting for every employee and runs in O(n) time with a single aggregation pass.
Is Find the Team Size asked at Google/Amazon/Meta?
Problems involving SQL aggregation and joins are common in database interview rounds at companies like Amazon, Google, and Meta. Variants of this question test your ability to combine GROUP BY with joins and understand relational query structure.
What data structure is used in Find the Team Size?
The problem relies on relational database operations rather than traditional in-memory data structures. SQL engines internally use hash or sort-based aggregation for GROUP BY and join algorithms to combine tables.
What is the time complexity of Find the Team Size?
The optimal GROUP BY + join approach runs in O(n) time because the database scans the Employee table once to compute counts and then performs a simple join. A self-join approach can degrade toward O(n²) if many employees belong to the same team.