This is a sample of a tree structure in a single table that I’m working on. The table is a hierarchy of entities, each generating a certain amount of emissions. There are three fields relating to the amount of emissions generated by each entity: emission, emission_locationbased, and emission_marketbased. If an entity has a non-null emission value, then its emission_locationbased and emissions_marketbased values are null. If an entity has a null emission value, then it has both an emission_locationbased and emissions_marketbased value.

The issue is taking percentContribution into account. It indicate how many percentage of that entity’s cumulative emission (both _locationbased and _marketbased) that node contribute to its parent’s cumulative sum.

For example:

This is how I calculated cumsum_locationbased and cumsum_marketbased:

WITH base_data AS (
    SELECT 
        entityId,
        parentId,
        emission,
        emission_locationbased ,
        emissions_marketbased ,
        array_agg(entityId) OVER (ORDER BY parentId) AS arr
    FROM entity
),

cumulative_sums AS (
    SELECT
        entityId,
        parentId,
        emission,
        emission_locationbased ,
        emissions_marketbased ,
        SUM(COALESCE(emission, emission_locationbased , 0)) 
            OVER (ORDER BY arr)  AS cumsum_locationbased, 
        SUM(COALESCE(emission, emissions_marketbased , 0)) 
            OVER (ORDER BY arr) AS cumsum_marketbased  
    FROM base_data
    GROUP BY entityId, parentId, emission, emission_locationbased, emissions_marketbased, arr
)

SELECT
    entityId,
    parentId,
    emission,
    CASE WHEN emission IS NOT NULL THEN NULL ELSE emission_locationbased END AS emission_locationbased ,
    CASE WHEN emission IS NOT NULL THEN NULL ELSE emissions_marketbased END AS emissions_marketbased,
    cumsum_locationbased,
    cumsum_marketbased
FROM cumulative_sums

I has been a while since I last touch SQL so I can’t figure out what to do.