I have the above tree structure, my goal is to calculate the cumulative sum of each node, with weighted contribution from the children node.

I first tried to write a bottom up hierarchical cumulative sum query without taking percentContribution into account first, where the query will traverse the tree from the leaf node up:

WITH RECURSIVE C(id, Amount, RootID,tenantid) AS (
    SELECT 
        entityid AS id,
        emissioni AS Amount,
        entityid AS RootID ,
        tenantid
    FROM tree_table
    WHERE entityId NOT IN (
        SELECT DISTINCT parentId FROM tree_table WHERE parentId IS NOT NULL)

    UNION ALL

    SELECT 
        T.parentid AS id,
        C.Amount + T.emission AS Amount,
        C.RootID,
        C.tenantid
    FROM tree_table AS T
    INNER JOIN C ON T.entityid = C.id AND T.tenantid = C.tenantid
)
SELECT 
    T.entityid AS entityid,
    T.parentid,
    T.emission,
    S.AmountIncludingChildren AS total_carbonAmount_marketbased,
    T.name,
    T.category,
    T.scope,
    T.tenantid,
    T.activityid
FROM joined_nuoa_table AS T
INNER JOIN (
             SELECT id, SUM(Amount) AS AmountIncludingChildren
             FROM C
             GROUP BY id
            ) AS S ON T.entityId = S.id
ORDER BY T.entityId

Expected result:

Actual result:

I’m not too familiar with bottom-up traversal, so I’m not entire sure what went wrong.

Expected result after taking into account percentContribution