Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TEMP TABLE tree (
  id_reach integer PRIMARY KEY,
  from_node integer UNIQUE NOT NULL,
  to_node integer,
  area float
);

INSERT INTO tree(id_reach, from_node, to_node, area) VALUES
 (0, 1, 0, 40),
 (1, 3, 1, 29),
 (2, 2, 1, 5),
 (3, 4, 3, 7),
 (4, 5, 3, 6); 

CREATE TABLE



INSERT 0 5


WITH RECURSIVE us_path AS (
    select *
    from (
        SELECT * , row_number() over (order by area desc) maxarea       
        FROM tree
        WHERE to_node = 0
    ) t where maxarea = 1 
    UNION ALL
    select * 
    from (select tr.*,row_number() over (order by tr.area desc) maxarea
    FROM tree as tr
    join us_path AS usp 
        ON tr.to_node = usp.from_node
    ) t where maxarea = 1 
     
)
SELECT id_reach, area
FROM us_path;


id_reach
area


0
40

1
29

3
7



SELECT 3


WITH RECURSIVE us_path AS (
    select *
    from (
        SELECT to_node, from_node, id_reach,area , row_number() over (partition by to_node order by area desc) maxarea       
        FROM tree
    ) t where maxarea = 1 
    UNION ALL
    select * 
    from (select usp.to_node, tr.from_node,tr.id_reach,tr.area,row_number() over (partition by usp.to_node order by tr.area desc) maxarea
    FROM tree as tr
    join us_path AS usp 
        ON tr.to_node = usp.from_node
    ) t where maxarea = 1 
)
SELECT to_node, array_agg(id_reach), sum(area) total_area
FROM us_path
group by to_node
order by to_node


to_node
array_agg
total_area


0
{0,1,3}
76

1
{1,3}
36

3
{3}
7



SELECT 3