Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE Stage_History(
   DateDay VARCHAR(5) NOT NULL
  ,id      VARCHAR(1) NOT NULL
  ,Stage   VARCHAR(11) NOT NULL
  ,Amount  INTEGER  NOT NULL
);
INSERT INTO Stage_History(DateDay,id,Stage,Amount) VALUES ('Day 1','A','Open',50);
INSERT INTO Stage_History(DateDay,id,Stage,Amount) VALUES ('Day 2','A','Negotiation',60);
INSERT INTO Stage_History(DateDay,id,Stage,Amount) VALUES ('Day 4','A','Closed',70);
INSERT INTO Stage_History(DateDay,id,Stage,Amount) VALUES ('Day 1','B','Open',10);
INSERT INTO Stage_History(DateDay,id,Stage,Amount) VALUES ('Day 3','B','Negotiation',30);
INSERT INTO Stage_History(DateDay,id,Stage,Amount) VALUES ('Day 4','B','Closed',40);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

SELECT * from Stage_History


dateday
id
stage
amount


Day 1
A
Open
50

Day 2
A
Negotiation
60

Day 4
A
Closed
70

Day 1
B
Open
10

Day 3
B
Negotiation
30

Day 4
B
Closed
40


-- EXPECTED RESULT
CREATE TABLE Stage_by_Day(
   DateDay VARCHAR(5)  NOT NULL
  ,Stage   VARCHAR(11) NOT NULL
  ,Amount  INTEGER  NOT NULL
);
INSERT INTO Stage_by_Day(DateDay,Stage,Amount) VALUES ('Day 1','Open',60);
INSERT INTO Stage_by_Day(DateDay,Stage,Amount) VALUES ('Day 1','Negotiation',0);
INSERT INTO Stage_by_Day(DateDay,Stage,Amount) VALUES ('Day 1','Closed',0);
INSERT INTO Stage_by_Day(DateDay,Stage,Amount) VALUES ('Day 2','Open',10);
INSERT INTO Stage_by_Day(DateDay,Stage,Amount) VALUES ('Day 2','Negotiation',60);
INSERT INTO Stage_by_Day(DateDay,Stage,Amount) VALUES ('Day 2','Closed',0);
INSERT INTO Stage_by_Day(DateDay,Stage,Amount) VALUES ('Day 3','Open',0);
INSERT INTO Stage_by_Day(DateDay,Stage,Amount) VALUES ('Day 3','Negotiation',90);
INSERT INTO Stage_by_Day(DateDay,Stage,Amount) VALUES ('Day 3','Closed',0);
INSERT INTO Stage_by_Day(DateDay,Stage,Amount) VALUES ('Day 4','Open',0);
INSERT INTO Stage_by_Day(DateDay,Stage,Amount) VALUES ('Day 4','Negotiation',0);
INSERT INTO Stage_by_Day(DateDay,Stage,Amount) VALUES ('Day 4','Closed',110);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

SELECT *
FROM Stage_by_Day 


dateday
stage
amount


Day 1
Open
60

Day 1
Negotiation
0

Day 1
Closed
0

Day 2
Open
10

Day 2
Negotiation
60

Day 2
Closed
0

Day 3
Open
0

Day 3
Negotiation
90

Day 3
Closed
0

Day 4
Open
0

Day 4
Negotiation
0

Day 4
Closed
110


select d.DateDay, s.stage, coalesce(sh.amount, 0)
from (select distinct sh.DateDay from stage_history sh) d cross join
     (select distinct sh.stage from stage_history sh) s left join lateral
     (select sum(amount) as amount
      from (select distinct on (id) sh.*
            from stage_history sh
            where sh.DateDay <= d.DateDay 
            order by id, sh.DateDay desc
           ) sh
      where sh.stage = s.stage
     ) sh
     on 1=1
order by d.DateDay, s.stage


dateday
stage
coalesce


Day 1
Closed
0

Day 1
Negotiation
0

Day 1
Open
60

Day 2
Closed
0

Day 2
Negotiation
60

Day 2
Open
10

Day 3
Closed
0

Day 3
Negotiation
90

Day 3
Open
0

Day 4
Closed
110

Day 4
Negotiation
0

Day 4
Open
0


select d.DateDay, s.stage, coalesce(sh.amount, 0)
from (select distinct sh.DateDay from stage_history sh) d cross join
     (select distinct sh.stage from stage_history sh) s left join lateral
     (select sum(amount) as amount
      from (select distinct on (id) sh.*
            from stage_history sh
            where sh.stage = s.stage and sh.DateDay <= d.DateDay 
            order by id, sh.DateDay desc
           ) sh
     ) sh
     on 1=1
order by d.DateDay, s.stage


dateday
stage
coalesce


Day 1
Closed
0

Day 1
Negotiation
0

Day 1
Open
60

Day 2
Closed
0

Day 2
Negotiation
60

Day 2
Open
60

Day 3
Closed
0

Day 3
Negotiation
90

Day 3
Open
60

Day 4
Closed
110

Day 4
Negotiation
90

Day 4
Open
60
dateday	id	stage	amount
Day 1	A	Open	50
Day 2	A	Negotiation	60
Day 4	A	Closed	70
Day 1	B	Open	10
Day 3	B	Negotiation	30
Day 4	B	Closed	40