-- create a table
CREATE TABLE Employee (
  employee_id INTEGER PRIMARY KEY,
  team_id INTEGER NOT NULL
);

-- insert some values
INSERT INTO Employee VALUES (1, 8);
INSERT INTO Employee VALUES (2, 8);
INSERT INTO Employee VALUES (3, 8);
INSERT INTO Employee VALUES (4, 7);
INSERT INTO Employee VALUES (5, 9);
INSERT INTO Employee VALUES (6, 9);

-- fetch some values
-- SELECT * FROM Employee;

select 
    employee_id, 
    count(employee_id) over(partition by team_id) as team_size 
from Employee
order by employee_id;