DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’» is a community of 966,155 amazing developers

We're a place where coders share, stay up-to-date and grow their careers.

Create account Log in
Aditya Nikhil
Aditya Nikhil

Posted on

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.
Image description

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  |
+----+-------+
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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**.
Enter fullscreen mode Exit fullscreen mode

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.

Image description

Top comments (0)

Take a look at this:

Settings

Go to your customization settings to nudge your home feed to show content more relevant to your developer experience level. πŸ›