[solvesql] 친구 수 집계하기

문제

제출

WITH a as (
  SELECT b.user_id,ifnull(count(user_b_id),0) num_friends1
  FROM edges a RIGHT JOIN users b on a.user_a_id = b.user_id
  GROUP BY 1
),
b as (
  SELECT b.user_id,ifnull(count(user_a_id),0) num_friends2
  FROM edges a RIGHT JOIN users b on a.user_b_id = b.user_id
  GROUP BY 1
)
SELECT a.user_id,num_friends1 + num_friends2 num_friends
FROM a
JOIN b ON a.user_id = b.user_id
GROUP BY a.user_id
ORDER BY 2 desc, 1 asc

🚨 error

1. edges 테이블에서 user_a_id로 group by 하여 user_b_id 카운트

   SELECT user_a_id user_id,count(user_b_id) num_friends
   FROM edges
   GROUP BY user_a_id
   ORDER BY 2 desc, 1 asc

   모든 user 출력되는 것이 아닌 user_a_id에 있는 user들만 출력됨
   → edges 테이블이 아닌 users 테이블에서 user 전부 불러와야 함

2. users 테이블에서 user 전부 불러온 후 user_a_id로 group by 하여 user_b_id 카운트

   WITH RECURSIVE id as (
     SELECT 1 as user_id
     UNION ALL
     SELECT user_id + 1
     FROM id
     WHERE user_id < (SELECT user_id FROM users ORDER BY 1 DESC LIMIT 1)
   )
   SELECT a.user_id,ifnull(count(user_b_id),0) num_friends
   FROM id a
   LEFT JOIN edges b
   ON a.user_id = b.user_a_id
   GROUP BY a.user_id
   ORDER BY 2 desc, 1 asc

   user_a_id → user_b_id 만이 아닌 user_b_id → user_a_id 인 친구 모두 뽑아야 함.
   → user_a_id로 group by 하여 user_b_id 카운트 + user_b_id로 group by 하여 user_a_id 카운트

3. user_a_id로 group by 하여 user_b_id 카운트, user_b_id로 group by 하여 user_a_id 카운트하는 쿼리 cte로 각각 만들기

   WITH RECURSIVE id as (
     SELECT 1 as user_id
     UNION ALL
     SELECT user_id + 1
     FROM id
     WHERE user_id < (SELECT user_id FROM users ORDER BY 1 DESC LIMIT 1)
   ),
   a as (
     SELECT user_a_id,count(user_b_id) num_friends1
     FROM edges
     GROUP BY 1
   ),
   b as (
     SELECT user_b_id,count(user_a_id) num_friends2
     FROM edges b
     GROUP BY 1
   )
   SELECT i.user_id,num_friends1 + num_friends2 num_friends
   FROM id i
   LEFT JOIN a ON i.user_id = a.user_a_id
   LEFT JOIN b ON i.user_id = b.user_b_id
   GROUP BY i.user_id
   ORDER BY 2 desc, 1 asc
   

   만약 a에 없는 user_id일 경우, b의 user_id의 num_friends2와 합할 수 없어 null 값이 됨
   → 카운트 값이 null일 경우 0이 되도록 처리를 해야 함