Capped Running Sum | Running Sum with Reset | Running Sum with Cap | Complex SQL Interview Question

Capped Running Sum | Running Sum with Reset | Running Sum with Cap | Complex SQL Interview Question

In this video, I am going to show you how you can solve this tricky sql question using Recursive CTE.
The question is to compute capped running sum, reset the sum whenever sum becomes negative.

---------
with data as (
select CAST("2012-01-01" as Date) as D_Date, 800 as Revenue UNION ALL
select "2012-02-01", 1900 UNION ALL
select "2012-03-01", 1750 UNION ALL
select "2012-04-01", -20000 UNION ALL
select "2012-05-01", 900 UNION ALL
select "2012-06-01", 3900 UNION ALL
select "2012-07-01", -2600 UNION ALL
select "2012-08-01", -2600 UNION ALL
select "2012-09-01", 21000 UNION ALL
select "2012-10-01", -2400 UNION ALL
select "2012-11-01", 1100 UNION ALL
select "2012-12-01" ,1300
)
---creating physical table named temp_table in temp_dataset.

CREATE OR REPLACE TABLE temp_dataset.temp_table
AS
select *,row_number() over (order by D_Date) as rn from data

Solution is in comments.

Thank you for watching.