Based on previous records, stop sum fields

January 6, 2022

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.

>Solution :

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