Retrieve data based on hierarchical references

Question

I have one table in which data is stored in hierarchical manner. In below table, we have parent for each child.

CHILD_ID	PARENT_ID
2	1
3	1
4	2
5	3
6	3
7	2
8	6
10	8
11	5
12	7
13	4

I have another table where we have interest of each child. Example as below

CHILD_ID	INTEREST
4	SPORTS
11	BOOKS
7	POLITICS
12	SPORTS
7	BOOKS
8	TV
12	POLITICS

Using a SQL query in Oracle, I want to retrieve count of interests under the hierarchy of ID as 2, i.e. it should consider 2 and its direct or grand children, i.e 4, 7, 12 & 13.

Output of the query should be 4.

Note: I tried to use connect_by_root but not successful in query creation.

Answer 1

Using an ancient connect by

with bom as (
   select distinct connect_by_root(parent_id) root, child_id
   from data
   start with parent_id = 2
   connect by prior child_id = parent_id
)
select b.root, count(distinct interest) ni
from bom b
join interests s on s.child_id in (b.root, b.child_id)
group by b.root

db<>fiddle