We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.

- Prepare
- SQL
- Advanced Select
- Binary Tree Nodes
- Discussions

# Binary Tree Nodes

# Binary Tree Nodes

#### Sort by

recency

#### |

#### 2185 Discussions

#### |

Please Login in order to post a comment

SELECT N, CASE WHEN P IS NULL THEN 'Root' WHEN N IN (SELECT DISTINCT P FROM BST) THEN 'Inner' ELSE 'Leaf' END AS TYPE FROM BST ORDER BY N;

no need of CTE in this problem.

you can do this using case when:-

select N, case when p is null then 'Root' when p is not null and n in (select p from BST) then 'Inner' else 'Leaf' end from BST order by N

Select DISTINCT a.N, CASE WHEN a.P is NULL THEN 'Root' WHEN b.N is NULL THEN 'Leaf' ELSE 'Inner' END From BST a LEFT JOIN BST b on a.N = b.P;

-- Define the root CTE to find nodes with no parent

WITH root AS ( SELECT DISTINCT N FROM BST WHERE P IS NULL ),

-- Define the inner_node CTE to find nodes that are parents of other nodes -- but are not classified as roots

inner_node AS ( SELECT DISTINCT P FROM BST

WHERE P NOT IN (SELECT N FROM root) ),

-- Define the leaf CTE to find nodes that are not parents of any nodes -- and have a parent (i.e., P is not NULL)

leaf AS ( SELECT DISTINCT N FROM BST WHERE N NOT IN (SELECT P FROM inner_node) AND P IS NOT NULL )

-- Combine results from all three CTEs and classify nodes as 'Root', 'Inner', or 'Leaf'

SELECT N, 'Root' FROM root

UNION ALL

SELECT P, 'Inner' FROM inner_node

UNION ALL

SELECT N, 'Leaf' FROM leaf

-- Order the final result by node value ORDER BY 1;