[Leetcode] Tree Node

문제 링크

정답

# Write your MySQL query statement below
WITH tree_with_child_cnt AS (
    SELECT 
        P.id, P.p_id,
        COUNT(C.p_id) OVER (PARTITION BY P.id) as child_cnt
    FROM Tree P
    LEFT JOIN Tree C
    ON P.id = C.p_id
)

SELECT 
    DISTINCT id,
    CASE
        WHEN p_id IS NULL THEN 'Root'
        WHEN child_cnt > 0 THEN 'Inner'
        ELSE 'Leaf'
    END as 'type'
FROM tree_with_child_cnt;

✅ Point

• child node 개수를 구하여 CTE 선언
• p_id 값이 NULL 인지 + child node 개수로 node type 결정
• CTE 선언 없이 child node 존재 여부만으로 node type 구분 가능

다른 풀이

• Subquery 로 child 존재 여부 확인

SELECT 
    id,
    CASE
        WHEN p_id IS NULL THEN 'Root'
        WHEN id IN (SELECT p_id FROM Tree) THEN 'Inner'
        ELSE 'Leaf'
    END as 'type'
FROM Tree

• JOIN 으로 child 존재 여부 확인

  SELECT 
    DISTINCT P.id,
    CASE
        WHEN P.p_id IS NULL THEN 'Root'
        WHEN C.id IS NULL THEN 'Leaf'
        ELSE 'Inner'
    END as 'type'
  FROM Tree P
  LEFT JOIN Tree C
  ON P.id = C.p_id