How to prevent negative revenue values in SQL and redistribute them to following months?

I have a revenue table in SQL with columns for date and revenue. I want to create a logic that prevents negative revenue values, so that any negative revenue value is set to 0 and the negative amount is subtracted from the revenue of following months until it is fully offset.

For example, if I have the following revenue data:

Date	Revenue
2018-09-01	1200
2018-08-01	400
2018-07-01	-1000
2018-06-01	800
2018-05-01	600
2018-04-01	200
2018-03-01	-200
2018-02-01	400
2018-01-01	200

The expected output should be:

Date	Revenue
2018-09-01	600
2018-08-01	0
2018-07-01	0
2018-06-01	800
2018-05-01	600
2018-04-01	0
2018-03-01	0
2018-02-01	400
2018-01-01	200

Here is some code to get started


WITH revenue_table AS (
    SELECT '2018-09-01' AS date, 1200 AS revenue UNION ALL
    SELECT '2018-08-01' AS date, 400 AS revenue UNION ALL
    SELECT '2018-07-01' AS date, -1000 AS revenue UNION ALL
    SELECT '2018-06-01' AS date, 800 AS revenue UNION ALL
    SELECT '2018-05-01' AS date, 600 AS revenue UNION ALL
    SELECT '2018-04-01' AS date, 200 AS revenue UNION ALL
    SELECT '2018-03-01' AS date, -200 AS revenue UNION ALL
    SELECT '2018-02-01' AS date, 400 AS revenue UNION ALL
    SELECT '2018-01-01' AS date, 200 AS revenue
)

select * from revenue_table

I'm not sure how to approach this problem. Can anyone suggest a solution or provide some sample code that achieves this logic?

tried to create a rolling sum on the negative values and substract the positive ones
Recursion, but seem very slow and did not really work

Solution

If I follow the question correctly, we do need recursion here, so that we can iteratively distribute negative amounts over the following rows.

least and greatest should be good enough for the arithmetic:

with recursive
    data as (
        select r.*, row_number() over(order by date) rn from revenue_table r
    ),
    rcte (date, old_revenue, rn, new_revenue, rest) as (
        select null::date, 0, 0::bigint, 0, 0
        union all
        select d.*, greatest(d.revenue + r.rest, 0), least(d.revenue + r.rest, 0)
        from rcte r
        inner join data d on d.rn = r.rn + 1
    )
select date, old_revenue, new_revenue 
from rcte 
where rn > 0 order by date desc

date	old_revenue	new_revenue
2018-09-01	1200	600
2018-08-01	400	0
2018-07-01	-1000	0
2018-06-01	800	800
2018-05-01	600	600
2018-04-01	200	0
2018-03-01	-200	0
2018-02-01	400	400
2018-01-01	200	200

fiddle