TS's 100DOC

Journal Archive

2022

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 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100

/* Write your PL/SQL query statement below */
WITH tree_expand AS
(
SELECT id,
       level AS node_level
FROM tree
START WITH p_id IS NULL
CONNECT BY PRIOR id = p_id
)

SELECT id,
       CASE WHEN node_level = 1 THEN 'Root'
            WHEN id NOT IN (SELECT p_id
                            FROM tree
                            WHERE p_id IS NOT NULL) THEN 'Leaf'
            ELSE 'Inner'
            END AS "Type"
FROM tree_expand
ORDER BY id

Day 97: Solving one of LeetCode problems

608. Tree Node Difficulty - Medium

Table: Tree

+-------------+------+
    | Column Name | Type |
    +-------------+------+
    | id          | int  |
    | p_id        | int  |
    +-------------+------+
    id is the primary key column 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 an SQL query to report the type of each node in the tree.

Return the result table ordered by id in ascending order.

The query result format is in the following example.

Example 1:

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  |
    +----+-------+
    Explanation: 
    Node 1 is the root node because its parent node is null and it has child nodes 2 and 3.
    Node 2 is an inner node because it has parent node 1 and child node 4 and 5.
    Nodes 3, 4, and 5 are leaf nodes because they have parent nodes and they do not have child nodes.

Example 2:

Input: 
    Tree table:
    +----+------+
    | id | p_id |
    +----+------+
    | 1  | null |
    +----+------+
    Output: 
    +----+-------+
    | id | type  |
    +----+-------+
    | 1  | Root  |
    +----+-------+
    Explanation: If there is only one node on the tree, you only need to output its root attributes.