Re: Reporting by family tree

Example Below:
-- create a test family tablecreate table family as(select 1 as id, null::integer as parent_id, 'Grandfather1' as nameunion allselect 2 as id, null::integer as parent_id, 'Grandfather2' as nameunion allselect 3 as id, 1 as parent_id, 'Father1-1' as nameunion allselect 4 as id, 1 as parent_id, 'Father1-2' as nameunion allselect 5 as id, 2 as parent_id, 'Father2-1' as nameunion allselect 6 as id, 3 as parent_id, 'Son1-1-1' as nameunion allselect 7 as id, 4 as parent_id, 'Son1-2-1' as nameunion allselect 8 as id, 5 as parent_id, 'Son2-1-1' as name);
-- create a test contribution tablecreate table contribution as(select 1 as contributor_id, '2020-01-01' as date, 300.00 as contribution_amountunion allselect 1 as contributor_id, '2020-02-01' as date, 255.00 as contribution_amountunion allselect 1 as contributor_id, '2020-03-01' as date, 45.65 as contribution_amountunion allselect 2 as contributor_id, '2020-05-01' as date, 22.55 as contribution_amountunion allselect 2 as contributor_id, '2020-01-01' as date, 450.00 as contribution_amountunion allselect 3 as contributor_id, '2020-02-01' as date, 200.00 as contribution_amountunion allselect 4 as contributor_id, '2020-03-01' as date, 150.00 as contribution_amountunion allselect 4 as contributor_id, '2020-04-01' as date, 60.45 as contribution_amountunion allselect 4 as contributor_id, '2020-05-01' as date, 300.00 as contribution_amountunion allselect 5 as contributor_id, '2020-06-01' as date, 1250.00 as contribution_amountunion allselect 6 as contributor_id, '2020-01-01' as date, 66.50 as contribution_amountunion allselect 7 as contributor_id, '2020-02-01' as date, 855.00 as contribution_amountunion allselect 8 as contributor_id, '2020-02-01' as date, 25.00 as contribution_amount);
-- execute recursive query, all children inheriting contribution sum of parentswith recursive cte as(select f.id, f.parent_id, f.name, c.contribution_amountfrom family fjoin contribution c on f.id = c.contributor_idunion allselect f.id, f.parent_id, f.name, cte.contribution_amountfrom ctejoin family f on cte.id = f.parent_id)select id, name, sum(contribution_amount) as total_contributionfrom ctegroup by id, nameorder by id;
-- execute recursive query, parents have sum of all contributions of its childrenwith recursive cte as(selectf.id,f.parent_id,f.name,c.contribution_amountfromfamily fjoin contribution c onf.id = c.contributor_idunion allselectf.id,f.parent_id,f.name,cte.contribution_amountfromctejoin family f oncte.parent_id = f.id)selectid,name,sum(contribution_amount) as total_contributionfromctegroup byid,nameorder byid;

Change your SQL accordingly. Also, you can add month field to yield results per month.