leetcode - 1303. Find the Team Size

난이도 : Easy

 

Table: Employee

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| employee_id   | int     |
| team_id       | int     |
+---------------+---------+
employee_id is the primary key for this table.
Each row of this table contains the ID of each employee and their respective team.

 

Write an SQL query to find the team size of each of the employees.

Return result table in any order.

 

 

SELECT employee_id
     , cnt team_size
FROM Employee
     JOIN (
           SELECT team_id
                , COUNT(*) cnt
           FROM Employee
           GROUP BY team_id
           ) sub ON Employee.team_id = sub.team_id

Accepted (50.14%)

 

 

윈도우 함수를 활용한 방법

SELECT employee_id
     , COUNT(employee_id) OVER (PARTITION BY team_id) team_size
FROM Employee

Accepted (87.18%)