WITH RECURSIVE tree AS (
    SELECT id, name, parent_id
    FROM assets
    WHERE parent_id IS NULL
    UNION ALL
    SELECT a.id, a.name, a.parent_id
    FROM assets a
    JOIN tree t ON a.parent_id = t.id
),
agg AS (
    SELECT
        a.id AS asset_id,
        SUM(e.downtime_hours * e.prod_rate + e.parts_cost + e.labor_cost +
            e.env_cost + e.reg_cost + e.safety_cost) AS event_cost
    FROM assets a
    LEFT JOIN events e ON a.id = e.asset_id
    GROUP BY a.id
)
SELECT 
    t.id,
    t.name,
    (SELECT SUM(event_cost)
     FROM agg x
     WHERE x.asset_id IN (
         -- all descendants + self
         WITH RECURSIVE d AS (
             SELECT id FROM assets WHERE id = t.id
             UNION ALL
             SELECT a.id FROM assets a
             JOIN d ON a.parent_id = d.id
         )
         SELECT id FROM d
     )) AS rolled_cost
FROM tree t
ORDER BY rolled_cost DESC;