[Day 24] solvesql | Advent of SQL 2024 | 이상 사용자 탐지💡

세 명이 서로 친구인 관계 찾기

✔️0215 복습

 

소셜 네트워크 분석에서는 세 명의 사용자가 서로 친구 관계인 경우를 중요하게 생각한다.

 

일반적인 사용자는 세 명의 사용자가 서로 친구인 경우가 다수 있지만,  스팸 사용자 또는 친구 관계를 무작위로 맺는 사용자의 경우 전체 친구 수에 비해 세 명의 사용자가 친구인 경우가 많지 않아 이상 사용자 탐지에 유용하게 쓸 수 있기 때문이다.

 

문제 조건

1. ID가 3820인 사용자를 포함해 세 명의 사용자가 친구 관계인 경우를 출력

2. 중복된 세 친구 관계를 제외하기 위해 user_a_id < user_b_id < user_c_id를 만족하도록 출력

 

아이디어

 

Solution

WITH T1 AS (
  SELECT e.user_a_id,
         e.user_b_id,
         e2.user_c_id
  FROM edges e
  JOIN (SELECT user_a_id as user_b_id,
               user_b_id as user_c_id
        FROM edges) e2 ON e.user_b_id = e2.user_b_id
  WHERE 3820 IN (user_a_id, e.user_b_id, user_c_id)
  )
  SELECT t.user_a_id,
         t.user_b_id,
         t.user_c_id
  FROM edges e 
  JOIN T1 t ON e.user_a_id = t.user_a_id AND e.user_b_id = t.user_c_id
  
 
 /*
 WITH step1 AS (
  SELECT e1.user_a_id
       , e1.user_b_id
       , e2.user_b_id AS user_c_id -- ✔️join 방식 의도를 살펴보며는 (a,b)/(b,c)간에는 친구 관계임
  FROM edges e1
    JOIN edges e2 ON e1.user_b_id = e2.user_a_id
  WHERE 3820 IN (e1.user_a_id, e1.user_b_id, e2.user_b_id)

)
SELECT s.*
FROM step1 s 
  JOIN edges e ON s.user_a_id = e.user_a_id
              AND s.user_c_id = e.user_b_id
 */
 
 /*
 WITH step1 AS (
  SELECT e1.user_a_id
       , e1.user_b_id
       , e2.user_b_id AS user_c_id -- ✔️join 방식 의도를 살펴보며는 (a,b)/(b,c)간에는 친구 관계임
  FROM edges e1
    JOIN edges e2 ON e1.user_b_id = e2.user_a_id
  WHERE 3820 IN (e1.user_a_id, e1.user_b_id, e2.user_b_id)

)
SELECT *
FROM step1
WHERE (user_a_id, user_c_id) IN (
  SELECT *
  FROM edges
)
 */

 

▶ T1 CTE:

user_a_id와 user_b_id가 친구, user_b_id와 user_c_id가 친구인 관계인 경우를 출력한다.

 

▶ 최종 SELECT:

user_a_id와 user_c_id가 친구 관계에 속하여 3명 모두 친구 관계인 경우를 출력한다.

CTE와 비교해보면,

(3442, 3820) -> 친구 X

(3446, 3875) -> 친구 X

(3442, 3913) -> 친구 X

(3442, 3918) -> 친구 X 
임을 알 수 있다. 

 

 

✔️0215 복습