Oracle Cumulative or Incremental subtraction

My Table structure & data-

CREATE TABLE T_PTS_TEST
   (
        CITY_ID NUMBER,
        MNTH DATE,
        EARNS NUMBER,
        BURNS NUMBER        
   );
   
insert into T_PTS_TEST values( 125600, TO_DATE('2023-May-31', 'YYYY-Month-DD'), 600, -400 );
insert into T_PTS_TEST values( 125600, TO_DATE('2023-June-30', 'YYYY-Month-DD'), 400, -300 );
insert into T_PTS_TEST values( 125600, TO_DATE('2023-July-31', 'YYYY-Month-DD'), 800, -600 );
insert into T_PTS_TEST values( 125600, TO_DATE('2023-August-31', 'YYYY-Month-DD'), 500, 0 );
insert into T_PTS_TEST values( 125600, TO_DATE('2023-September-30', 'YYYY-Month-DD'), 900, 0 );

The table-

So, the Total burns for this particular city from May to September is -1300 i.e

select city_id, sum(burns) from T_PTS_TEST group by city_id

I want to have a cumulative subtraction of this total_burns from each earns as follows-

Total Deductions = -1300

Cumulative Deductions
May  -  600 -1300 = -700
June -  400 -700  = -300
July -  800 -300  = 500
Aug  -  500
Sep  -  900

The query output should look like-

I can achieve the above by looping inside a PL/SQL block or Function, but how can I do the same using select statements? I tried using different ways like sum(..) over and lag. But unable to achieve it.

Please help.

You can use the SUM analytic function to find the cumulative earns-per-month and the total burns and then use the LEAST function to find the lesser of the total and the current month's earns:

SELECT city_id,
       mnth,
       earns,
       LEAST(
         SUM(earns) OVER (PARTITION BY city_id ORDER BY mnth)
         + SUM(burns) OVER (),
         earns
       ) AS total
FROM   t_pts_test t

Which, for the sample data, outputs:

You could also use a MODEL clause:

SELECT city_id, mnth, earns, total
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (PARTITION BY city_id ORDER BY mnth) AS rn
  FROM   t_pts_test t
)
MODEL
  PARTITION BY ( city_id )
  DIMENSION BY ( rn )
  MEASURES     ( mnth, earns, burns, 0 AS total )
  RULES (
    total[1]    = earns[1] + Sum(burns)[rn],
    total[rn>1] = LEAST(total[CV()-1], 0) + earns[CV()]
  )

(But I'm not sure that after numbering the rows and then performing the SUM in the first row of each partition that it may be less efficient when compared to using the first query with just analytic functions; you could profile both solutions and check.)

fiddle