Description

Table: Employee

Column NameType
employee_idint
team_idint
  • 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.

Problem Statement

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_idteam_id
18
28
38
47
59
69

Output:

employee_idteam_size
13
23
33
41
52
62

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.

Solution

There are couple of approaches to solve this problem.

  1. Using Aggregated table per team and joining with the Employee table.
  2. Using Window functions to count per team

1. Using Aggregated Table

The most intuitive way probably is to calculate the number of employees per team.

1SELECT team_id, COUNT(1) AS team_size
2    FROM Employee
3    GROUP BY team_id
team_idteam_size
83
71
92

This returns correct team size per team_id. Now, you can use this table to join with existing Employees table to find each employee’s team size. This would give you the final result.

1WITH team_sizes AS (
2    SELECT team_id, COUNT(1) AS team_size
3        FROM Employee
4        GROUP BY team_id
5) SELECT e.employee_id, ts.team_size
6    FROM Employee e
7    JOIN team_sizes ts
8    ON e.team_id = ts.team_id;

2. Using Window function

Alternatively, you can create different partitions per team_id to find the number of employees per team.

1SELECT employee_id, 
2    COUNT(1) OVER (PARTITION BY team_id) AS team_size
3    FROM Employee;