awesome-leetcode-sql/leetcode/medium/608. Tree Node.sql at afba6c35c3f6bcbef1da8e5d6dfa0fb54f2bdcd7 · Ivanbyone/awesome-leetcode-sql · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
/*
Question 608. Tree Node
Link: https://leetcode.com/problems/tree-node/description/?envType=problem-list-v2&envId=database

Table: Tree

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| p_id        | int  |
+-------------+------+
id is the column with unique values for this table.
Each row of this table contains information about the id of a node and the id of its parent node in a tree.
The given structure is always a valid tree.


Each node in the tree can be one of three types:

"Leaf": if the node is a leaf node.
"Root": if the node is the root of the tree.
"Inner": If the node is neither a leaf node nor a root node.
Write a solution to report the type of each node in the tree.

Return the result table in any order.
*/

SELECT
    t.id,
    (CASE
        WHEN t.p_id IS NULL THEN 'Root'
        WHEN t.id IN (
            SELECT DISTINCT t1.p_id
            FROM Tree AS t1
        ) THEN 'Inner'
        ELSE 'Leaf'
    END) AS type --noqa: RF04
FROM Tree AS t