Count children from hierarchy path

Question

I have a table like this:

id	name	path
1	John	/1
2	Mark	/2
3	Kevin	/1/3
4	Sarah	/1/3/4
5	Andy	/2/5
...	...	...

So, I can say that Sarah is Kevin's child which is John's child.

I would like to have this:

id	name	path	number of children
1	John	/1	2
2	Mark	/2	1
3	Kevin	/1/3	1
4	Sarah	/1/3/4	0
5	Andy	/2/5	0
...	...	...	...

TASK NUMBER 2: Let's say that I have this table too

id	income	user_id
1	200	1
2	120	1
3	340	2
4	500	3
5	600	5
6	80	5

I can say that John has a Total income of 320$, but if I also want to count John's children, it is 820$ (because id =3 is John's child). So, I would also like a query where I can count all the hierarchical incomes.

Answer 1

You can do:

select
  t.*,
  (select count(*) from t c where c.path like t.path || '/%') as c_count,
  i.income + (
    select coalesce(sum(i.income), 0) from t c join i on i.user_id = c.id
     where c.path like t.path || '/%'
  ) as c_income
from t
left join (
  select user_id, sum(income) as income from i group by user_id
) i on i.user_id = t.id

Result:

 id  name   path    c_count  c_income 
 --- ------ ------- -------- -------- 
 1   John   /1      2        820      
 2   Mark   /2      1        1020     
 3   Kevin  /1/3    1        500      
 4   Sarah  /1/3/4  0        null     
 5   Andy   /2/5    0        680      

See example at DB Fiddle.