Classifying binary tree nodes using SQL

Question

I am trying to solve the problem mentioned at the below link:

https://www.hackerrank.com/challenges/binary-search-tree-1/problem

I have written the following code. Please help me where i am going wrong

Select q.Node,case
              WHEN q.Node NOT IN q.Parent THEN 'Leaf' 
              WHEN q.Node IN q.Parent AND q.Node NOT IN q.Higher_Parent THEN 'Inner'
              WHEN q.Node IN q.Parent AND q.Node IN q.Higher_Parent THEN 'Root'
              END as NodeType
from (
SELECT B1.N as Node,B1.P as Parent,B2.P as Higher_Parent FROM 
BST B1 INNER JOIN BST B2 
ON B1.P = B2.N
ORDER BY Node ASC
) q

N P HP
1 2 5
2 5 NULL
3 2 5
6 8 5
8 5 NULL
9 8 5

Where should I modify the above code to work. Sure there are other concise codes for the same problem but please help me with this code for learning purpose.

Bijay Shakya · Accepted Answer · 2020-01-07 22:20:08Z

2

You can use CASE statement as follows:

SELECT N, CASE WHEN P IS NULL THEN 'Root' 
WHEN(SELECT COUNT(*) FROM BST WHERE P = T.N) > 0 THEN 'Inner'
ELSE 'Leaf'
END
FROM BST T
ORDER BY N;

answered Jan 7, 2020 at 22:20

Bijay Shakya

613 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

M_S_N · Accepted Answer · 2022-03-28 09:52:14Z

1

-- SQL SERVER working Solution

select N,
case 
when p is null then "Root"
when N in (select P from BST) THEN "Inner"
Else "Leaf" end as "P"
from BST
ORDER BY N;

edited Mar 28, 2022 at 9:52

M_S_N

2,8061 gold badge19 silver badges40 bronze badges

answered Jun 17, 2020 at 18:03

Sarath Puppala

111 bronze badge

Comments

Paul Spiegel · Accepted Answer · 2018-08-07 17:42:21Z

0

select n.n,
  case 
    when max(p.n) is null then 'root'
    when max(c.n) is null then 'leaf'
    else 'inner'
  end as type
from bst n
left join bst p on p.n = n.p
left join bst c on c.p = n.n
group by n.n

Result:

| n |  type |
|---|-------|
| 1 |  leaf |
| 2 | inner |
| 3 |  leaf |
| 5 |  root |
| 6 |  leaf |
| 8 | inner |
| 9 |  leaf |

Demo: http://sqlfiddle.com/#!9/ba3c76/1

answered Aug 7, 2018 at 17:42

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

Comments

Ajay Gupta · Accepted Answer · 2018-08-07 19:29:32Z

0

You need to find the child instead of the higher parent.

Select distinct c.n, 
        case when c.P is null then 'Root' 
             when b1.N is null then 'Leaf'
             else 'Inner' end
from BST c
left join BST b1
on c.N = b1.P
order by c.n

answered Aug 7, 2018 at 19:29

Ajay Gupta

1,8651 gold badge14 silver badges22 bronze badges

Comments

Kumar Attangudi Perichiappan · Accepted Answer · 2019-10-24 05:16:14Z

0

You can use if else to solve it as well:

SELECT B.N, IF(B.P IS NULL, 'Root', IF((SELECT COUNT(*) FROM BST AS A WHERE A.P=B.N)>0,'Inner','Leaf')) 
FROM BST AS B 
ORDER BY B.N;

answered Oct 24, 2019 at 5:16

Kumar Attangudi Perichiappan

263 bronze badges

Comments

Ankit · Accepted Answer · 2020-08-17 06:45:59Z

0

See the below code. It might be easy to comprehend.

select n, 'Leaf' as nodename from bst
where n not in (select distinct p from bst where p is not null)
union
select n, 'Root' as nodename from bst
where p is null
union
select n, 'Inner' as nodename from bst
where n in (select distinct p from bst)
and p is not null
order by n asc;

answered Aug 17, 2020 at 6:45

Ankit

114 bronze badges

Comments

Penny Liu · Accepted Answer · 2024-12-23 07:15:26Z

0

Here’s a explanation of the query:

SELECT N,
(CASE
   WHEN P IS NULL THEN 'Root'
   WHEN N IN (SELECT DISTINCT P FROM BST) THEN 'Inner'
   ELSE 'Leaf'
END) AS Output
FROM BST
ORDER BY N;

CASE Statement:

If P IS NULL: Classify the node as 'Root' (it has no parent).
If N IN (SELECT DISTINCT P FROM BST): Classify the node as 'Inner' (it is a parent to at least one other node).
Otherwise: Classify the node as 'Leaf' (it is neither a root nor a parent).

Final Output

answered Dec 23, 2024 at 7:15

Penny Liu

18k5 gold badges89 silver badges109 bronze badges

Collectives™ on Stack Overflow

Classifying binary tree nodes using SQL

7 Answers 7

Comments

Comments

Comments

Comments

Comments

Comments

Final Output

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

Comments

Comments

Comments

Comments

Comments

Final Output

Comments

Your Answer

Sign up or log in

Post as a guest

Related