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,
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
I solved it using
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)
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,
atreeas an Alias to our Tree table.
idfrom 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**.
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,
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.