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,
- Sets
atree
as an Alias to our Tree table. - 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.
Top comments (0)