친구 수 집계하기 - solvesql

친구 수 집계하기

출처 : 친구 수 집계하기 SolveSQL

내 답안📕

WITH new_edges AS (
  SELECT user_a_id AS a
       , user_b_id AS b
  FROM edges
  UNION
  SELECT user_b_id AS a
       , user_a_id AS b
  FROM edges
)

SELECT u.user_id AS user_id
     , COUNT(ne.a) AS num_friends
FROM users AS u
  LEFT JOIN new_edges AS ne ON u.user_id = ne.a
GROUP BY u.user_id
ORDER BY num_friends DESC, user_id ASC;