Based on previous records, stop sum fields

Question

I have a table like this:

|id |   date     | point |
+---+------------+-------+
| 6 | 2022/01/06 |   9   |
| 5 | 2022/01/05 |   7   |
| 4 | 2022/01/04 |   1   |
| 3 | 2022/01/03 |   4   |
| 2 | 2022/01/02 |   6   |
| 1 | 2022/01/01 |   1   |

My goal is to obtain sum of points from date now to date that the point is greater than equal to 1 and stop sum of fields before that date.

In the example above (order by date), the answer is sum of points date now(2022/01/06) to the date 2022/01/04 and equals 9 + 7 + 1 = 17. Because the point value for date 2022/01/03 is equal to 4, therefore is not calculated in sum.

I read about CTE and Window Functoin however, I wasn't able to implement them properly.

What database and version are you using? – Schwern Jan 06 '22 at 19:19 — Schwern, Jan 06 '22 at 19:19
@Schwern Postgres database and latest version – Saeed Jan 06 '22 at 19:21 — Saeed, Jan 06 '22 at 19:21

score 0 · Accepted Answer · answered Jan 06 '22 at 19:27

Find the first row before (and including) the desired date whose point value is 1.
Sum up that and everything newer than it up to (and including) the desired date.

select sum(point)
from points
where date between (
  select "date"
  from points
  where point = 1 and date <= '2022/01/06'
  order by "date" desc
  limit 1
) and '2022/01/06';

Demonstration

Based on previous records, stop sum fields

1 Answers1