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 employeeteam_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_idis the primary key for this table
Visualization
Tap to expand
Understanding the Visualization
1
Input
Employee table with employee_id and team_id
2
Group
Partition by team_id to group teammates
3
Count
Count members in each team and assign to all team members
Key Takeaway
🎯 Key Insight: Use window functions when you need aggregated data (team size) alongside individual row data (employee_id)
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code