Find the Team Size - Problem

Given an Employee table containing employee IDs and their respective team IDs, write a SQL solution to find the team size for each employee.

Table: Employee

  • employee_id (int): Primary key, unique identifier for each employee
  • team_id (int): The team that the employee belongs to

Return the result table showing each employee with their team size in any order.

Table Schema

Employee
Column Name Type Description
employee_id PK int Primary key, unique identifier for each employee
team_id int The team that the employee belongs to
Primary Key: employee_id
Note: Each row represents one employee and their team assignment

Input & Output

Example 1 — Multiple Teams
Input Table:
employee_id team_id
1 8
2 8
3 9
4 9
5 9
Output:
employee_id team_size
1 2
2 2
3 3
4 3
5 3
💡 Note:

Team 8 has 2 employees (1 and 2), so both get team_size = 2. Team 9 has 3 employees (3, 4, and 5), so all three get team_size = 3.
Example 2 — Single Employee Team
Input Table:
employee_id team_id
1 10
2 11
3 11
Output:
employee_id team_size
1 1
2 2
3 2
💡 Note:

Employee 1 is alone in team 10 (team_size = 1). Employees 2 and 3 are both in team 11 (team_size = 2).

Constraints

  • 1 ≤ employee_id ≤ 100
  • 1 ≤ team_id ≤ 100
  • employee_id is the primary key for this table

Visualization

Tap to expand
Find the Team Size INPUT Employee Table employee_id team_id 1 8 2 8 3 8 4 7 5 9 6 9 Team 8: emp 1,2,3 Team 7: emp 4 Team 9: emp 5,6 ALGORITHM STEPS 1 Create Subquery COUNT employees per team 2 GROUP BY team_id Aggregate team sizes 3 JOIN with Employee Match on team_id 4 SELECT Result Return emp_id + team_size SELECT e.employee_id, t.team_size FROM Employee e JOIN (...) t ON e.team_id = t.team_id FINAL RESULT Output Table employee_id team_size 1 3 2 3 3 3 4 1 5 2 6 2 OK - Complete! Each employee now has their team size attached Key Insight: The optimal solution uses a self-join or window function. Using COUNT(*) OVER (PARTITION BY team_id) eliminates the need for a subquery, computing team size directly for each row in a single pass - O(n) time. TutorialsPoint - Find the Team Size | Optimal Solution

Related Problems

Asked in
Amazon 12 Facebook 8 Microsoft 6
33.0K Views
Medium Frequency
~8 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen