Advented of SQL 2024 : 친구 수 집계하기 (DAY 22)

문제 탐구

데이터베이스에 포함된 모든 사용자에 대해 각 사용자의 친구 수를 집계해 출력하는 쿼리를 작성하기

• edges 테이블에는 사용자의 친구 관계 정보가 들어있는데 각 행의 user_a_id 컬럼 사용자와 user_b_id 컬럼 사용자가 서로 친구 관계라는 의미

• users 테이블 존재. 해당 테이블에는 모든 user정보 나와있음

• 쿼리 결과에는 아래 컬럼이 포함되어 있어야 하고, 친구 수가 많은 사용자부터 출력되어야 합니다. 만약 친구 수가 같은 사용자가 여럿이라면 그 사이에서는 사용자 ID가 작은 사용자가 먼저 출력되어야 합니다.

📍출력 컬럼
user_id: 사용자 ID
num_friends: 친구 수

과정

1번째 시도

#오답:총 행의 값은 4039

with cte_1 as (select * from edges 
union ALL
select user_b_id as user_a_id , user_a_id as user_b_id from edges),
cte_2 as (select user_a_id, user_b_id from cte_1 group by user_a_id, user_b_id),
cte_3 as (select user_a_id as user_id ,count(*) as num_friends
from cte_2
group by user_a_id
order by 2 desc, 1 asc)
select count(*) from cte_3;

📌오답 원인
행의 값이 다르게 나왔다. 실제는 4039의 행이지만 나는 3천 후반대의 행이 나왔다. 그러면 특정 user값이 포함이 안됐다는 것이다.
다시 확인해보니 user 테이블이 있었다!
-> user테이블하고 조인해서 친구 수가 없는 행도 출력하도록 하였다.

2번째 시도

#오답: 3863 레코드 값이 다르다. 제출은 12,정답은 34

with cte_1 as (select * from edges 
union ALL
select user_b_id as user_a_id , user_a_id as user_b_id from edges),
cte_2 as (select user_a_id, user_b_id from cte_1 group by user_a_id, user_b_id),
cte_3 as (select distinct u.user_id as user_id ,count(*) as num_friends
from cte_2 c2 right join users u on c2.user_a_id =  u.user_id
group by u.user_id
order by 2 desc, 1 asc)

📌오답 원인
3863 레코드 값이 달랐다. 3863번째 행에 34가 나와야하는데 나는 12로 나왔다. 확인해보니 count()로 계산해서 잘못된 것이다.
user_id을 기준으로 출력된 친구 수를 카운팅 해야한다. 단순히 출력된 행의 개수를 카운팅하면 안된다!
-> count()을 count(user_b_id)으로 변경했다.

3번째 시도 (정답 코드)

with cte_1 as (select * from edges 
union ALL
select user_b_id as user_a_id , user_a_id as user_b_id from edges),
cte_2 as (select user_a_id, user_b_id from cte_1 group by user_a_id, user_b_id),
cte_3 as (select u.user_id as user_id ,count(user_b_id) as num_friends
from cte_2 c2 right join users u on c2.user_a_id =  u.user_id
group by u.user_id
order by 2 desc, 1 asc)

select * from cte_3;