SQL | SOVESQL 2024 🎄: Day 22. 친구 수 집계하기

Day 22. 친구 수 집계하기

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

with t1 as (
  (select user_a_id as user_id
    , count(*) as num_friends
  from edges
  group by user_a_id
  order by user_a_id)

  union all

  (select user_b_id as user_id
    , count(*) as num_friends
  from edges
  group by user_b_id
  order by user_b_id)
), t2 as (
  select u.user_id
    , case when num_friends is not null then num_friends
        else 0 end as num_friends
  from users as u 
    left join t1 on u.user_id = t1.user_id
)

select user_id, sum(num_friends) as num_friends
from t2
group by user_id
order by num_friends desc, u.user_id asc

❗USER_ID 기준으로 GROUP BY 

 

 

 

+) WITH 절 : T1

❗UNION 사용해서 두 개의 테이블 위 아래로 결합하기
❗USER_ID A로 GROUP BY 한 것, B로 GROUP BY 한 것 합침
❗USER_A_ID에 있어도 USER_B_ID에 없어서 한 번에 다 있는 데이터를 만들어야 함

 

 

 

+) WITH 절 : T2

❗CASE WHEN 사용해서 친구 수가 존재하지 않으면 0 삽입
❗LEFT JOIN 사용해서 주어진 테이블과 만든 T1 테이블 결합