Answer to HackerRank's "Binary Tree Nodes" SQL practice problem

binary-search-tree-1.sql

--  https://www.hackerrank.com/challenges/binary-search-tree-1/problem
select
  parents.n,
  case
    -- if there is no parent to this node, then it is the root of the tree
    when parents.p is null then 'Root'
    -- if there are no children to this node, then it is a leaf
    when children.nodes is null then 'Leaf'
    -- otherwise it much be a branch
    else 'Inner'
  end as word
from bst parents
left join (
  select count(n) nodes, p parent from bst group by parent order by parent
) as children on children.parent = parents.n
order by parents.n;