[SQL] solvesql Advent of SQL 2024 22일차

# 링크

• solvesql Advent of SQL 2024: https://solvesql.com/collections/advent-of-sql-2024/

https://solvesql.com/collections/advent-of-sql-2024/

• 22일차 - 친구 수 집계하기: https://solvesql.com/problems/number-of-friends/

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

 

# 풀이

WITH
  normalized_edges AS (
    SELECT
      CASE
        WHEN user_a_id < user_b_id THEN user_a_id
        ELSE user_b_id
      END AS user1,
      CASE
        WHEN user_a_id < user_b_id THEN user_b_id
        ELSE user_a_id
      END AS user2
    FROM
      edges
  ),
  friend_counts AS (
    SELECT
      user1 AS user_id
    FROM
      normalized_edges
    UNION ALL
    SELECT
      user2 AS user_id
    FROM
      normalized_edges
  ),
  aggregated_counts AS (
    SELECT
      user_id,
      COUNT(*) AS num_friends
    FROM
      friend_counts
    GROUP BY
      user_id
  )
SELECT
  u.user_id,
  COALESCE(a.num_friends, 0) AS num_friends
FROM
  users u
  LEFT JOIN aggregated_counts a ON u.user_id = a.user_id
ORDER BY
  num_friends DESC,
  u.user_id ASC;

• normalized_edges CTE
  • SELECT
    • CASE WHEN user_a_id < user_b_id THEN user_a_id ELSE user_b_id END AS user1: user_a_id가 user_b_id보다 작은 경우 user_a_id를 반환하고, 그렇지 않은 경우 user_b_id를 반환하여 user1로 저장한다.
    • CASE WHEN user_a_id < user_b_id THEN user_b_id ELSE user_a_id END AS user2: user_a_id가 user_b_id보다 작은 경우 user_b_id를 반환하고, 그렇지 않은 경우 user_a_id를 반환하여 user_a_id로 저장한다.
  • FROM edges: edges 테이블에서 데이터를 가져온다.
•   friend_counts CTE
  
  • SELECT user1 AS user_id: user1 컬럼을 user_id로 저장한다.
  • FROM normalized_edges: normalized_edges CTE에서 데이터를 가져온다.
  • UNION ALL: 데이터를 결합한다. (중복 데이터 유지)
  • SELECT user2 AS user_id: user2 컬럼을 user_id로 저장한다.
  • FROM normalized_edges: normalized_edges CTE에서 데이터를 가져온다.
•   aggregated_counts CTE
  
  • SELECT
    
    • user_id: user_id 컬럼을 선택한다.
    • COUNT(*) AS num_friends: 데이터의 개수를 계산하여 num_friends로 저장한다.
  • FROM friend_counts: friend_counts CTE에서 데이터를 가져온다.
  • GROUP BY user_id: user_id 컬럼을 기준으로 그룹화한다.
• SELECT
  
  • u.user_id: users 테이블의 user_id 컬럼을 선택한다.
  • COALESCE(a.num_friends, 0) AS num_friends: aggregated_counts CTE 컬럼의 num_friends 컬럼을 선택하고, NULL 값을 0으로 대체하여 num_friends로 저장한다.
• FROM users u: users 테이블의 별칭을 u로 설정하고, 해당 테이블에서 데이터를 가져온다.
• LEFT JOIN aggregated_counts a ON u.user_id = a.user_id: aggregated_counts CTE의 별칭을 a로 설정하고, user_id 컬럼을 기준으로 하여 users 테이블에 aggregated_counts CTE를 LEFT JOIN 한다. 
• ORDER BY num_friends DESC, u.user_id ASC: num_friends 컬럼을 기준으로 내림차순 정렬하고, num_friends가 같은 경우 users 테이블의 user_id를 기준으로 오름차순 정렬한다.