[해커랭크/SQL] 35. Binary Tree Nodes

👉https://www.hackerrank.com/challenges/binary-search-tree-1/problem?isFullScreen=true

Binary Tree Nodes | HackerRank

---

 

🤔 문제 분석

1. You are given a table, BST, containing two columns: N and P, where N represents the value of a node in Binary Tree, and P is the parent of N.

2. Write a query to find the node type of Binary Tree ordered by the value of the node. Output one of the following for each node:

• Root: If node is root node.
• Leaf: If node is leaf node.
• Inner: If node is neither root nor leaf node.

 

💡 풀이

SELECT N, IF(P IS NULL, "Root", "Inner")
FROM BST
WHERE N IN (
SELECT P
FROM(
    SELECT N, P, ROW_NUMBER() OVER (PARTITION BY P ORDER BY N) AS cnt
    FROM BST
    ) AS sub1
WHERE cnt = 2
)

UNION ALL

SELECT N, "Leaf"
FROM BST
WHERE N NOT IN(
SELECT P
FROM(
    SELECT N, P, ROW_NUMBER() OVER (PARTITION BY P ORDER BY N) AS cnt
    FROM BST
    ) AS sub1
WHERE cnt = 2
)
ORDER BY N

 

+ 윈도우 함수를 쓰지 않고

IN에다가 DISTINCT를 사용하면 더 간단하게 작성할 수 있음