Solving Tree Node Problem with Correlated Subquery

Prerequisite: --------------------- Solve any Correlated Subquery by following these steps https://youtu.be/z7ipijJK52o Code: ----------- select id,case when count_value=0 and p_id is not null then "Leaf" when count_value=0 and p_id is null then "Root" when count_value!=0 and p_id is null then"Root" when count_value!=0 and p_id is not null then"Inner" end as type from ( select id,p_id,(select count(*) from Tree b where b.p_id=a.id) as count_value from Tree a) c; Check this playlist for more Data Engineering related videos: https://youtube.com/playlist?list=PLjfRmoYoxpNopPjdACgS5XTfdjyBcuGku