Sum a column using Recursive query in Redshift

--- Table definition CREATE TABLE rstable ( Person_owner_id TEXT, Person_id TEXT, Person_target INTEGER, Person_actual INTEGER, Date_description TEXT ); --- Recreating data as insert statement INSERT INTO rstable (Person_owner_id, Person_id, Person_target, Person_actual, Date_description) VALUES ('Bob', NULL, 18, 0, 'Jan-23'), ('Bob', NULL, 19, 0, 'Feb-23'), ('Bob', NULL, 8, 0, 'Mar-23'), ('Bob', NULL, 10, 0, 'Apr-23'), ('Bob', NULL, 12, 0, 'May-23'), ('Bob', NULL, 6, 0, 'Jun-23'), ('Bob', NULL, 4, 0, 'Jul-23'), ('Bob', NULL, NULL, 0, 'Aug-23'), ('Bob', NULL, NULL, 0, 'Sep-23'), ('Bob', NULL, NULL, 0, 'Oct-23'), ('Bob', NULL, NULL, 0, 'Nov-23'), ('Bob', NULL, NULL, 0, 'Dec-23'), ('Bob', 'Chris', 10, 9, 'Jan-23'), ('Bob', 'Chris', 10, 9, 'Feb-23'), ('Bob', 'Chris', 10, 7, 'Mar-23'), ('Bob', 'Chris', 10, 5, 'Apr-23'), ('Bob', 'Chris', 10, 6, 'May-23'), ('Bob', 'Chris', 10, 3, 'Jun-23'), ('Bob', 'Chris', 10, 2, 'Jul-23'), ('Bob', 'Chris', 10,NULL,'Aug-23'), ('Bob', 'Chris', 10,NULL,'Sep-23'), ('Bob', 'Chris', 10,NULL,'Oct-23'), ('Bob', 'Chris', 10,NULL,'Nov-23'), ('Bob', 'Chris', 10,NULL,'Dec-23'), ('Bob','Ali' ,10 ,9 ,'Jan-23' ), ('Bob','Ali' ,10 ,9 ,'Feb-23' ), ('Bob','Ali' ,10 ,7 ,'Mar-23' ), ('Bob','Ali' ,10 ,5 ,'Apr-23' ), ('Bob','Ali' ,10 ,6 ,'May-23' ), ('Bob','Ali' ,10 ,3 ,'Jun-23' ), ('Bob','Ali' ,10 ,2 ,'Jul-23' ), ('Bob','Ali' ,10,NULL,'Aug-23' ), ('Bob','Ali' ,10,NULL,'Sep-23' ), ('Bob','Ali' ,10,NULL,'Oct-23' ), ('Bob','Ali' ,10,NULL,'Nov-23' ), ('Bob','Ali' ,10,NULL,'Dec-23'); 

The query

WITH RECURSIVE t( Person_owner_id, Person_id, Person_target, Person_actual, Date_description, SUM_ACTUAL ) AS ( SELECT Person_owner_id, Person_id, Person_target, Person_actual, Date_description, SUM(Person_actual) OVER ( PARTITION BY Person_owner_id ORDER BY Date_description ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS SUM_ACTUAL FROM ( SELECT Person_owner_id, Person_id, Person_target, Person_actual, Date_description FROM rstable ) t WHERE Person_owner_id IS NOT NULL ) SELECT t.Person_owner_id, t.Person_id, t.Person_target, t.Person_actual, t.Date_description, t.SUM_ACTUAL FROM t; 

We first create a recursive CTE called t. It starts with the rows where the Person_owner_id is NULL. These rows represent the top-level managers. The query then calculates the cumulative sum of the Person_actual column for each manager and their subordinates. The query continues to iterate through the t CTE until there are no more rows and here's the output

I was able to get the results I wanted with following query:

SELECT t1.person_owner_id, t1.date_from, COALESCE(SUM(t2.person_actual), 0) AS sum_person_actual FROM ( SELECT DISTINCT person_owner_id, date_from FROM staging.rstable ) AS t1 CROSS JOIN ( SELECT DISTINCT person_id FROM staging.rstable WHERE person_id IS NOT NULL ) AS subordinates LEFT JOIN staging.rstable AS t2 ON t1.person_owner_id = t2.person_owner_id AND t1.date_from = t2.date_from AND t2.person_id = subordinates.person_id GROUP BY t1.person_owner_id, t1.date_from ORDER BY t1.person_owner_id, t1.date_from;