5 line SQL - No redundancy, easiest to read
Code\npostgresql []\nselect id,\n    case when p_id is null then \'Root\'\n         when id in (select p_id from tree) then \'Inner\'\n         else \'Leaf\' en
# Code\n```postgresql []\nselect id,\n    case when p_id is null then \'Root\'\n         when id in (select p_id from tree) then \'Inner\'\n         else \'Leaf\' end as type\nfrom tree\n```\n\n# Intuition\nCASE statements in SQL use short circuiting behavior, meaning the second line of the case statement will not be evaluated unless the first line evaluates to `false`. \n\nNo aggregation or joining is necessary since we will evaluate each row using the in-place entity relationship.\n\n**Step 1:** In a well formed tree, only the `Root` node will have a missing parent id (`p_id is null`). This logic does not require a join.\n\n**Step 2:** Following this, we now know that the next case statement will not evaluate a `Root` node. In this case, if the `id` is the parent of any other node in the tree (`id in (select p_id from tree))`, we know that this node is an inner node.\n\n**Step 3:** Since we now know that the node we are evaluating is not the root node, and not a parent of another node, the final node type can only be a `Leaf`. That is because it is the parent of no further nodes in the tree.\n
['MySQL', 'PostgreSQL']