[SQL문제풀기] 친구 수 집계하기

문제

solvesql.com

https://solvesql.com/problems/number-of-friends/

SQLite

내가 작성한 오답1

select user_a_id user_id, count(user_b_id) num_friends
from edges
group by user_a_id
order by 2 desc, 1

내가 작성한 오답2

a와 b가 친구일 때 한쪽에만 기록이 되므로 a와 b 두 경우를 모두 세어야 했다.

select u.user_id, count(e.user_b_id) num_friends
from edges e
right join users u on e.user_a_id = u.user_id
group by u.user_id
order by 2 desc, 1

내가 작성한 정답

with a as( select u.user_id, count(distinct e.user_b_id) num_friends
          from edges e
          right join users u on e.user_a_id = u.user_id
          group by u.user_id),
     b as(select u.user_id, count(distinct e.user_a_id) num_friends
          from edges e
          right join users u on e.user_b_id = u.user_id
          group by u.user_id)
select a.user_id, a.num_friends+b.num_friends num_friends
from a a
join b b on a.user_id = b.user_id
order by 2 desc, 1