Find the Team Size - Problem

You're working as a database analyst for a company that wants to understand team dynamics better. Given an Employee table that maps each employee to their team, your task is to find the team size for each employee.

The Employee table has the following structure:

Column NameType
employee_idint
team_idint

Where employee_id is the primary key (unique values) and each row contains an employee's ID and their respective team ID.

Goal: Write a SQL query to return each employee along with the size of their team.

Example:

If Team A has 3 members and Team B has 2 members, then every employee in Team A should show team_size = 3, and every employee in Team B should show team_size = 2.

Input & Output

example_1.sql — Basic Team Setup
$ 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 |
💡 Note: Team 8 has 3 employees (1,2,3), so each gets team_size=3. Team 7 has 1 employee (4), so employee 4 gets team_size=1. Team 9 has 2 employees (5,6), so each gets team_size=2.
example_2.sql — Single Employee Teams
$ Input: Employee table: | employee_id | team_id | |-------------|----------| | 1 | 10 | | 2 | 11 | | 3 | 12 |
› Output: | employee_id | team_size | |-------------|----------| | 1 | 1 | | 2 | 1 | | 3 | 1 |
💡 Note: Each employee is on their own team (teams 10, 11, 12), so all team sizes are 1.
example_3.sql — Large Team
$ Input: Employee table: | employee_id | team_id | |-------------|----------| | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 1 | | 5 | 1 |
› Output: | employee_id | team_size | |-------------|----------| | 1 | 5 | | 2 | 5 | | 3 | 5 | | 4 | 5 | | 5 | 5 |
💡 Note: All employees belong to team 1, which has 5 members, so everyone gets team_size=5.

Visualization

Tap to expand
Team AlphaE1E2E3Size: 3Team BetaE4Size: 1Team GammaE5E6Size: 2Final ResultE1 → team_size: 3E2 → team_size: 3E3 → team_size: 3E4 → team_size: 1E5 → team_size: 2E6 → team_size: 2Window function COUNT(*) OVER (PARTITION BY team_id) efficiently calculates team sizes
Understanding the Visualization
1
Group by Teams
Partition employees based on their team_id
2
Count Team Members
Use window function to count employees in each team partition
3
Assign Team Size
Each employee receives their team's total count as team_size
Key Takeaway
🎯 Key Insight: Window functions allow us to perform aggregate calculations (like COUNT) within partitions of data while still returning individual rows, making them perfect for this type of "group summary per individual" problem.

Time & Space Complexity

Time Complexity
⏱️
O(n)

Single scan through the table with efficient partitioning

n
2n
✓ Linear Growth
Space Complexity
O(n)

Output table has same size as input, no additional space for joins

n
2n
⚡ Linearithmic Space

Related Problems

Constraints

  • 1 ≤ employee_id ≤ 105
  • 1 ≤ team_id ≤ 105
  • employee_id is unique (primary key)
  • The result can be returned in any order
Asked in
Meta 45 Amazon 38 Google 32 Microsoft 28
28.5K Views
High 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