MYSQL/오답노트

solvesql (LV.3)친구 수 집계하기, 양방향 관계는 UNION ALL을!

수스리 2025. 6. 24. 15:42

문제

Facebook 소셜 네트워크 데이터베이스는 Facebook 서비스에서 샘플링한 사용자의 메타 정보와 사용자 사이의 친구 관계를 담고 있습니다. edges 테이블에는 사용자의 친구 관계 정보가 들어있는데 각 행의 user_a_id 컬럼 사용자와 user_b_id 컬럼 사용자가 서로 친구 관계라는 의미 입니다.

데이터베이스에 포함된 모든 사용자에 대해 각 사용자의 친구 수를 집계해 출력하는 쿼리를 작성해주세요. 쿼리 결과에는 아래 컬럼이 포함되어 있어야 하고, 친구 수가 많은 사용자부터 출력되어야 합니다. 만약 친구 수가 같은 사용자가 여럿이라면 그 사이에서는 사용자 ID가 작은 사용자가 먼저 출력되어야 합니다.

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

 

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

 

solvesql.com

기존 내 코드

SELECT user_id, COUNT(user_b_id) AS num_friends
FROM users U
LEFT JOIN edges E ON U.user_id = E.user_a_id
GROUP BY user_id
ORDER BY COUNT(user_b_id) DESC, user_id

이 코드의 문제점은 LEFT JOINp에 있다. 이 조인은 "사용자가 user_a_id로 나타나는 친구 관계만" 찾고 있다.

 

예시

users 테이블:

user_id
1
2  
3

edges 테이블:

user_a_id | user_b_id
    1     |    2      (1과 2가 친구)
    3     |    1      (3과 1이 친구)

 

  • 사용자 1의 친구는 두명 (2번과 3번)
  • 사용자 2의 친구는 한명 (1번)
  • 사용자 3의 친구는 한명 (1번)

하지만 기존 코드로 COUNT를 한다면 

 

  • 사용자 1: 1명 (실제로는 2명이어야 함)
  • 사용자 2: 0명 (실제로는 1명이어야 함)
  • 사용자 3: 1명 (정확함)

핵심문제 파악하기

가장중요한 문제는 내가 단방향 조인을 했다는 점이다. 친구 관계는 양방향이지만, 쿼리는 한 방향만 보고 있다. 사용자가 user_b_id로 나타나는 친구관계는 완전 무시된다. 

 

정답 쿼리

-- 올바른 해결책: 양방향 관계를 모두 고려
WITH all_friendships AS (
  -- 첫 번째 방향: user_a_id가 기준 사용자인 경우
  SELECT user_a_id AS user_id, user_b_id AS friend_id FROM edges
  UNION ALL
  -- 두 번째 방향: user_b_id가 기준 사용자인 경우 (역방향)
  SELECT user_b_id AS user_id, user_a_id AS friend_id FROM edges
)
SELECT U.user_id, COUNT(AF.friend_id) AS num_friends
FROM users U
LEFT JOIN all_friendships AF ON U.user_id = AF.user_id
GROUP BY U.user_id
ORDER BY COUNT(AF.friend_id) DESC, U.user_id;

-- 위 예시 데이터로 all_friendships CTE 결과:
-- user_id | friend_id
--    1    |    2      (원래 1→2 관계)
--    3    |    1      (원래 3→1 관계)  
--    2    |    1      (역방향 2←1 관계)
--    1    |    3      (역방향 1←3 관계)

-- 최종 정확한 결과:
-- user_id | num_friends
--    1    |     2      (친구: 2, 3)
--    2    |     1      (친구: 1)
--    3    |     1      (친구: 1)

 

해결책

단방향 조인을 양방향 조인으로 바꿔준다. 그러려면 UNUON ALL을 써서 user_a의 친구 수 user_b의 친구 수 모두를 구해준다. 

핵심 실수

단방향 조인만 사용해서 한족 방향의 친구로만 계산 했다. 친구 관계는 양방향

복기

친구 관계 같은 양방향 데이터를 다를 때는 항상 UNION을 사용해서 양방향을 모두 고려하자. "A와 B의 친구라면 B도 A의 친구다"

소셜 네트워크 문제에서는 테이블 구조가 한 방향으로 저장 되어 있어도 실제 관계는 양방향일 가능성을 항상 고려해야한다.

이런 문제를 만나면 이 관계가 양방향인지 단방향인지 생각해보자!

 

<2025년 6월 25일 복습>

 

복습에서 이런 쿼리가 나왔다.

WITH friend_ship AS(
 SELECT user_a_id AS user_id, user_b_id AS friend_id
FROM edges
UNION ALL
SELECT user_b_id AS user_id, user_a_id AS friend_id
FROM edges
)
SELECT F.user_id, COUNT(friend_id) AS num_friends
FROM users U
LEFT JOIN friend_ship F ON F.user_id = U.user_id
GROUP BY F.user_id
ORDER BY COUNT(friend_id) DESC, F.user_id

이 쿼리는 자세히 보면 문제가 있다. 그룹바이와 SELECT을 F.user_id로 했다. friend_ship은 친구가 없는 사용자는 안나온다. 이 둘을 users.user_id로 바꿔야 한다. GROUP BY와 SELECT을 신중하게 해야 한다는 중요한 교훈을 얻었다.