Running total of positive and negative numbers where the sum cannot go below zero

This is an SQL question.

I have a column of numbers which can be positive or negative, and I’m trying to figure out a way to have a running sum of the column, but where the total cannot go below zero.

Date       | Number | Desired | Actual
2020-01-01 |    8   |   8     |    8
2020-01-02 |   11   |  19     |   19
2020-01-03 |   30   |  49     |   49
2020-01-04 |  -10   |  39     |   39
2020-01-05 |  -12   |  27     |   27
2020-01-06 |   -9   |  18     |   18
2020-01-07 |  -26   |   0     |   -8
2020-01-08 |    5   |   5     |   -3
2020-01-09 |  -23   |   0     |  -26
2020-01-10 |   12   |  12     |  -14
2020-01-11 |   14   |  26     |    0

I have tried a number of different window functions on this, but haven’t found a way to prevent the running total from going into negative numbers.

Any help would be greatly appreciated.

---

EDIT – Added a date column to indicate the ordering

Answer

Unfortunately, there is no way to do this without cycling through the records one-by-one. That, in turn, requires something like a recursive CTE.

with t as (
      select t.*, row_number() over (order by date) as seqnum
      from mytable t
     ),
     cte as (
      select NULL as number, 0 as desired, 0 as seqnum
      union all
      select t.number,
             (case when cte.desired + t.number < 0 then 0
                   else cte.desired + t.number
              end),
             cte.seqnum + 1
      from cte join
           t
           on t.seqnum = cte.seqnum + 1
     )
select cte.*
from cte
where cte.number is not null;

I would recommend this approach only if your data is rather small. But then again, if you have to do this, there are not many alternatives other then going through the table row-by-agonizing-row.

Here is a db<>fiddle (using Postgres).