[HackerRank][SQL(Oracle)] Binary Tree Nodes

 

※  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.

 

■  [Advanced Select]  Binary Tree Nodes

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

Q.

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.

A.

SELECT
	N,
	CASE
		WHEN MAX_LEVEL = 1 THEN 'Root'
		WHEN MAX_LEVEL = (SELECT MAX(LEVEL) FROM BST CONNECT BY PRIOR N = P) THEN 'Leaf'
		ELSE 'Inner'
	END AS INFO
FROM
	(
		SELECT		N, P, MAX(LEVEL) MAX_LEVEL
		FROM		BST
		CONNECT BY	PRIOR N = P
		GROUP BY	N, P
	)
ORDER BY N;