Watch 3 video solutions for Find the Team Size, a easy level problem involving Database. This walkthrough by Everyday Data Science has 8,862 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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.
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 | 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. |
