This SQL solution is GENIUS!!🤩

I recently started off solving SQL problems on Leetcode and I come across atleast one problem every day whose solutions I think are pure genius.

It's one such problem I came across today which I'll discuss below,

Databases (608. Tree Node)

Problem statement
We need to find the type of each node in a tree. Whether it's a root, inner or a leaf node.

Input: 
Tree table:
+----+------+
| id | p_id |
+----+------+
| 1  | null |
| 2  | 1    |
| 3  | 1    |
| 4  | 2    |
| 5  | 2    |
+----+------+
Output: 
+----+-------+
| id | type  |
+----+-------+
| 1  | Root  |
| 2  | Inner |
| 3  | Leaf  |
| 4  | Leaf  |
| 5  | Leaf  |
+----+-------+

Checkout the description thoroughly from the link above and try get an idea of solving it before checking out below approaches

1. My approach

I solved it using CASE.
I don't want to go into nitty gritty details of it but here's an overview,
1.1 If p_id is null, then it's a Root node.
1.2 If id is in p_id, then it's an Inner node.
1.3 Else, it's a Leaf node.

2. Genius approach (Atleast I think)

Using If function

Here's the code,

SELECT
    atree.id,
    IF(ISNULL(atree.p_id),
        'Root',
        IF(atree.id IN (SELECT p_id FROM tree), 'Inner','Leaf')) Type
FROM
    tree atree
ORDER BY atree.id

Let's break it down,

1. Sets atree as an Alias to our Tree table.
2. Selects id from the Tree table.

Here's what's interesting!!!!
3.Creates an If function

General usage of If function 👇

`If(10>27, YES, NO)` 
It states that if condition `10>27` is true then **YES** else **NO**.

Now,
IF(ISNULL(atree.p_id),'Root'
states that,
if p_id is null, then set it as Root.
(And just like that our condition from 1.1 was satisfied)
4.Yet another If function embedded inside the first If function as an Else condition,
IF(atree.id IN (SELECT p_id FROM tree), 'Inner','Leaf')
This states that,
If id is in p_id set it as 'Inner' else as 'Leaf'.

(And just like that our conditions 1.2, 1.3 were satsified🤩)

The genius behind this solution is designing it by utilizing of simple functions ordered in a perfect way.
As complex as it may seem, as simple it is I believe.

If you made it till here, let me know what you think of this and also mention down in the comments if there are any more problems/solutions you find interesting.