구름 아카이브

저작권 문제로 인해, 직접 작성한 쿼리문만 공개.

22. 친구 수 집계하기

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

#### 1.WHERE NOT IN 활용

  (
  SELECT 
    user_id,
    COUNT(*) AS num_friends
  FROM(
    SELECT user_a_id AS user_id FROM edges
    UNION ALL
    SELECT user_b_id AS user_id FROM edges
  ) combined

  GROUP BY user_id

)

UNION ALL

  (
  SELECT 
    user_id,
    0 AS num_friends
  FROM users
  WHERE user_id NOT IN (
      SELECT user_a_id AS user_id FROM edges
      UNION
      SELECT user_b_id AS user_id FROM edges
  )

  GROUP BY user_id

)
ORDER BY num_friends DESC, user_id ASC







#### 2.LEFT JOIN 활용

-- 1. edges에서 모든 사용자에 대해 친구 수 계산
WITH friend_counts AS (
  
  SELECT 
      user_id,
      COUNT(*) AS num_friends
  FROM (
      SELECT user_a_id AS user_id FROM edges
      UNION ALL
      SELECT user_b_id AS user_id FROM edges
  ) combined
  GROUP BY user_id
)

-- 2. users 테이블의 모든 사용자 가져오기
, all_users AS (
  SELECT user_id FROM users
)

-- 3. 모든 사용자와 친구 수 결합
SELECT 
    all_users.user_id,
    COALESCE(friend_counts.num_friends, 0) AS num_friends
FROM all_users
  LEFT JOIN friend_counts ON all_users.user_id = friend_counts.user_id
ORDER BY num_friends DESC, all_users.user_id ASC