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 table_name(date TIMESTAMP, point INT, reset_date BOOL);
Insert Into table_name Values('2019-01-01 00:00:00',1,true),
('2019-01-02 00:00:00',3,false),
('2019-01-03 00:00:00',1,false),
('2019-01-04 00:00:00',2,false),
('2019-01-05 00:00:00',1,true),
('2019-01-06 00:00:00',4,false),
('2019-01-07 00:00:00',2,false);
CREATE TABLE
INSERT 0 7
Select date, point, reset_date,
SUM(point) Over (Partition By grp Order By date) As cumulative_point
From
(
Select *,
SUM(Case When reset_date Then 1 Else 0 End) Over (Order By date) As grp
From table_name
) T
Order By date
| date | point | reset_date | cumulative_point |
|---|---|---|---|
| 2019-01-01 00:00:00 | 1 | t | 1 |
| 2019-01-02 00:00:00 | 3 | f | 4 |
| 2019-01-03 00:00:00 | 1 | f | 5 |
| 2019-01-04 00:00:00 | 2 | f | 7 |
| 2019-01-05 00:00:00 | 1 | t | 1 |
| 2019-01-06 00:00:00 | 4 | f | 5 |
| 2019-01-07 00:00:00 | 2 | f | 7 |
SELECT 7