[SQL/solvesql LV4] 세 명이 서로 친구인 관계 찾기

https://solvesql.com/problems/friend-group-of-3/

https://solvesql.com/problems/friend-group-of-3/

 

🎯 문제 상황

소셜 네트워크 데이터를 분석하는 과정에서, 세 명의 사용자가 서로 친구 관계를 맺고 있는 "삼각형 구조"를 찾아야 합니다. 주어진 edges 테이블에는 (A, B) 형태의 친구 관계만 저장되어 있습니다.

 

우리의 목표는:

1. 두 단계를 거쳐 (A, B, C) 형태의 친구 관계를 찾고,
2. 최종적으로 (A, C) 관계까지 확인하여 실제 삼각관계를 필터링하는 것입니다.
3. 특정 사용자(예: 3820)가 포함된 삼각형만 출력합니다.

 

🎯 문제 해결 흐름

Step 1: A-B-C 관계 찾기

• edges 테이블을 두 번 조인하여 (A → B → C) 관계를 생성합니다.
• E1.user_a_id → E1.user_b_id → E2.user_b_id 순서로 친구 연결을 확장합니다.
• 결과적으로 (A, B, C) 형태의 데이터셋을 만듭니다.

이렇게 하면 user_a_id → user_b_id → user_c_id 연결 구조가 만들어집니다.

 

Step 2: A-C 관계 확인

이제 (A, C) 관계가 실제로 존재하는지 검증해야 합니다. edges 테이블과 다시 조인하여 (A, C) 또는 (C, A) 관계가 있는 경우만 필터링합니다.

이렇게 하면 (A, C) 관계가 존재하는 삼각형만 남게 됩니다.

 

Step 3: 최종 결과 필터링

• 같은 삼각형이 여러 번 나오는 것을 방지하기 위해 user_a_id < user_b_id < user_c_id 조건을 추가합니다.
• 특정 사용자가 포함된 삼각형만 출력합니다 (예: 3820).

이제 3820이 포함된 모든 친구 삼각형이 출력됩니다.

 

🎯쿼리

WITH ab AS (
    SELECT E1.user_a_id, E1.user_b_id, E2.user_b_id AS user_c_id
    FROM edges AS E1
    JOIN edges AS E2 
        ON E1.user_b_id = E2.user_a_id
),
ac AS (
    SELECT ab.user_a_id, ab.user_b_id, ab.user_c_id
    FROM ab
    JOIN edges AS E3
        ON ((ab.user_a_id = E3.user_a_id AND ab.user_c_id = E3.user_b_id) 
         OR (ab.user_a_id = E3.user_b_id AND ab.user_c_id = E3.user_a_id))
)

SELECT * 
FROM ac
WHERE user_a_id < user_b_id 
AND user_b_id < user_c_id
AND 3820 IN (user_a_id, user_b_id, user_c_id)

 

🎯핵심 문법 설명

📝 1. SELF JOIN 활용

• 같은 테이블을 두 번 이상 조인하여 관계를 확장할 수 있습니다.
• 본 문제에서는 edges 테이블을 두 번 조인하여 A-B-C 관계를 찾고, 한 번 더 조인하여 A-C 관계를 확인했습니다.

📝 2. OR 조건을 활용한 대칭 관계 확인

• 친구 관계는 (A, B) 또는 (B, A)로 저장될 수 있습니다.
• (A, C) 관계 확인 단계에서 OR 조건을 추가하여 대칭적 친구 관계를 고려했습니다.

 

🎯 마무리

이번 SQL 쿼리는 소셜 네트워크에서 삼각관계(클러스터링 구조)를 찾는 문제를 해결했습니다. WITH AS를 활용한 구조화된 접근 방식을 사용하여:

1. (A → B → C) 관계를 구성하고,
2. (A, C) 관계를 확인하여 삼각형 필터링한 후,
3. 3820이 포함된 삼각형만 출력하는 방식으로 문제를 해결했습니다.

이 방법을 활용하면, 친구 네트워크의 밀도를 분석하거나 추천 시스템을 설계하는 데에도 활용할 수 있습니다!