Description
Write a SQL query to find the team size for each employee. Return employee_id and team_size. **Output columns (in order):** employee_id, team_size
Table:
employees| Column | Type |
|---|---|
| employee_id | INT |
| team_id | INT |
Examples
Input:
CREATE TABLE employees (employee_id INT, team_id INT); INSERT INTO employees VALUES (1, 1), (2, 1), (3, 1), (4, 2);Output:
1|3
2|3
3|3
4|1Explanation:
Employees 1, 2, 3 are in team 1 (team size 3). Employee 4 is alone in team 2 (team size 1).
Input:
CREATE TABLE employees (employee_id INT, team_id INT); INSERT INTO employees VALUES (5, 10), (6, 20), (7, 20), (8, 20), (9, 30), (10, 30);Output:
5|1
6|3
7|3
8|3
9|2
10|2Explanation:
Employee 5 is alone in team 10 (team size 1). Employees 6, 7, and 8 are all in team 20 (team size 3). Employees 9 and 10 are both in team 30 (team size 2). This demonstrates multiple teams of different sizes.
Input:
CREATE TABLE employees (employee_id INT, team_id INT); INSERT INTO employees VALUES (11, 100), (12, 200), (13, 300), (14, 400), (15, 500);Output:
11|1
12|1
13|1
14|1
15|1Explanation:
Each employee is in their own unique team (teams 100, 200, 300, 400, 500). Every employee has a team size of 1, showing the edge case where all teams have only one member.
Constraints
- •
Use subquery or window function