how to reset cumulative sum when reached to threshold

Question

i stucked in problem that i calculate the cum_sum to amount column and i want whenever the cum_sum passed threshold 300 to reset the cum_sum and recalculate

the data is look like this

|pk | amount  |
|:-|:-:| ---:|
|1   | 1000   |
|2   |20      |
|3   |50      |
|4   |100     |
|5   |120     |
|6   |50      |
|7   |200     |
|8   |100     |
|9   |1000    |
|10  |200     |

and the expected output

|pk | amount | cum_sum| 
|:-|:-:| ---:|
|1   | 1000   |1000|
|2   |20      |20|
|3   |50      |70 | 
|4   |100     |170|
|5   |120     |290|
|6   |50      |340|
|7   |200     |200|
|8   |100     |300|
|9   |1000    |1000|
|10  |200     |200|

Answer 1

Because of the nature of this problem, you need to use a recursive CTE. This looks something like:

with t as (
      select t.*, row_number() over (order by pk) as seqnum
      from yourtable t
     ),
     cte as (
      select seqnum, pk. amount, amount as running_amount
      from t
      where seqnum = 1
      union all
      select t.seqnum, t.pk, t.amount,
             (case when running_amount + amount > 300 then amount
                   else running_amount + amount
              end)
      from cte join
           t
           on t.seqnum = cte.seqnum + 1
     )
select *
from cte;

The exact syntax for recursive CTEs varies, depending on the database, but they are part of standard SQL.