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.
  1. Prepare
  2. SQL
  3. Advanced Select
  4. Binary Tree Nodes
  5. Discussions

Binary Tree Nodes

  • coolshreyash0412
     + 0 comments

    I complicated it a little. But how do you guys think this solution looks:

    WITH p_val AS (
    SELECT DISTINCT p FROM bst
),
all_parents AS (
    SELECT LISTAGG(p, '-') WITHIN GROUP (ORDER BY p) AS all_parents
    FROM p_val
)
SELECT 
    bst.n,
    CASE 
        WHEN bst.p IS NULL THEN 'Root'
        WHEN CONCAT('-', CONCAT(all_parents.all_parents, '-')) LIKE '%-' || bst.n || '-%' THEN 'Inner'
        ELSE 'Leaf'
    END AS node_type
FROM bst
LEFT JOIN all_parents ON 1=1
ORDER BY bst.n;