문제

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

풀이 과정

1. 각 유저별 갯수 카운팅

a유저의 친구 수

WITH a_user AS(
  SELECT 
    u.user_id,
    COUNT(e.user_a_id) AS a_num_friends
  FROM users u
    LEFT JOIN edges e
    ON u.user_id = e.user_a_id
  GROUP BY
    u.user_id
), 

b유저의 친구 수

b_user AS(
  SELECT
    u.user_id,
    COUNT(e.user_b_id) AS b_num_friends
  FROM users u
    LEFT JOIN edges e
    ON u.user_id = e.user_b_id
  GROUP BY
    u.user_id
)

2. 각 유저별 친구 수를 하나의 컬럼에 표시

SELECT
  a.user_id,
  COALESCE(a_num_friends + b_num_friends,0) AS num_friends
FROM 
  a_user a
  INNER JOIN  b_user b
    ON a.user_id = b.user_id
ORDER BY
  2 DESC,
  1

전체 코드

WITH a_user AS(
  SELECT 
    u.user_id,
    COUNT(e.user_a_id) AS a_num_friends
  FROM users u
    LEFT JOIN edges e
    ON u.user_id = e.user_a_id
  GROUP BY
    u.user_id
), 
b_user AS(
  SELECT
    u.user_id,
    COUNT(e.user_b_id) AS b_num_friends
  FROM users u
    LEFT JOIN edges e
    ON u.user_id = e.user_b_id
  GROUP BY
    u.user_id
)

SELECT
  a.user_id,
  COALESCE(a_num_friends + b_num_friends,0) AS num_friends
FROM 
  a_user a
  INNER JOIN  b_user b
    ON a.user_id = b.user_id
ORDER BY
  2 DESC,
  1