How can I include an entry for an intermediate row In a recursive query summing up children

Question

I have a schema with activity(activity_id, parent_activity_id, name) and period(activity_id, duration, etc).

An activity can have ZERO to many associated periods. Further, activities have a reference to a parent references, making it hierarchical.

I am trying to write a query that will sum the durations per-activity and also "rollup" the periods from children, to the parent activity, and the grand-parent activity, recursively, so a parent's duration will include its own durations + the durations of all children (recursive). Initially the sum needs to be by day only, but I shall be adding by-week and by-month later.

The dbFiddle is a NEARLY working sample: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=c0e92e9ffe4454c2c69a8bdccc6abe75

The problem is that the value for row 7 (C2) needs to include the duration for it's children (C2 P1 and C2 P1 T1). An entry for C2 is not present because there are ZERO periods against the activity.

How can I update the query or views such that activities with ZERO periods still include the sum of its child activities?

Thanks, this SQL is killing my poor imperative brain!

row_number	activity_id	name	day_duration	date
1	1	C1	66	2021年07月26日 00:00:00.000000
2	1	C1	45	2021年07月27日 00:00:00.000000
3	2	C1 P1	34	2021年07月27日 00:00:00.000000
4	2	C1 P1	35	2021年07月26日 00:00:00.000000
5	3	C1 P1 T1	18	2021年07月26日 00:00:00.000000
6	3	C1 P1 T1	27	2021年07月27日 00:00:00.000000
7	4	C2	NULL	NULL
8	5	C2 P1	50	2021年07月27日 00:00:00.000000
9	6	C2 P1 T1	39	2021年07月26日 00:00:00.000000
10	6	C2 P1 T1	39	2021年07月27日 00:00:00.000000

• user212533: I don't have time to type up an answer at this moment, can you look at this and confirm the result? dbfiddle.uk/…
• David Kerr: @bbaird thank you for taking the time to answer my question but I'm afraid it's still missing the same rows (2021年07月27日 C2 and 2021年07月26日 C2 and C2 P1). I should also have said that there can be an arbitrary number of activity levels, from 1 to n. I'll update the question tomorrow when I'm back at the computer. Thanks again!
• user212533: I see the issue now, we don't have a list of dates that would apply to the top level of the hierarchy. Let me chew on it a little more and get back to you.
• user212533: Ok, I think I have something that produces what you're looking for but I'm not 100% if that's the best way. Take a look here to confirm the output is at least correct: dbfiddle.uk/…
• David Kerr: Thanks for that updated query; it does indeed work correctly. However the SQL is beyond me and I don't understand it and on this project I must understand every line of code (and write equivalent not just "read only"). I'm going to return the simple day durations and do the aggregation in Kotlin code, which will be a very few lines of code. The result set won't be any larger this way. I shall add your fiddle as an answer in case any one else asks something similar. Thanks again, it's very impressive what SQL can do!

Answer 1

@bbaird gave a working solution in this DbFiddle.

As per my comment to my question I have decided to return the day durations and do the nested summation in code as it will be a very few simple lines.