How to subtract the value from previous value

Question

I have a table like this:

DT	PRIN	INT
01-01-2023	100000	1100
01-02-2023	100000	1200
01-03-2023	100000	1500
01-04-2023	100000	2300

I need to subtract INT column from PRIN and get the subtracted amount in first row.
On second and following rows I need to also subtract INT values from all preceding rows.

The result should look like this:

DT	PRIN	INT	BAL
01-01-2023	100000	1100	98900
01-02-2023	100000	1200	97700
01-03-2023	100000	1500	96200
01-04-2023	100000	2300	93900

I have tried using LEAD and LAG functionality but cannot get subtraction correctly and this should be done in SQL.

Here is the script:

create table tbl9 ( dt date, prin varchar2(20), int varchar2(20));
insert into tbl9 values(to_date('01-01-2023','dd-mm-yyyy'),100000, 1100);
insert into tbl9 values(to_date('01-02-2023','dd-mm-yyyy'),100000, 1200);
insert into tbl9 values(to_date('01-03-2023','dd-mm-yyyy'),100000, 1500);
insert into tbl9 values(to_date('01-04-2023','dd-mm-yyyy'),100000, 2300);
commit;

Answer 1

You can use a Window Function SUM() for that

SELECT dt,prin, int, prin - SUM(int) OVER(PARTITION BY prin ORDER BY dt) bal
FROM tbl9

DT	PRIN	INT	BAL
01-JAN-23	100000	1100	98900
01-FEB-23	100000	1200	97700
01-MAR-23	100000	1500	96200
01-APR-23	100000	2300	93900

fiddle

Answer 2

If PRIN is the same in every row then use @nbk's answer.

To answer the question:

I need to subtract INT column from PRIN and get the subtracted amount in first row. On second and following rows I need to also subtract INT values from all preceding rows.

You need to always ensure that the cumulative sum is subtracted from the PRIN value in the first row (rather than the PRIN value from the current row).

You can either use the FIRST_VALUE and SUM analytic function:

SELECT dt,
       prin,
       int,
       FIRST_VALUE(prin) OVER (ORDER BY dt)
         - SUM(int) OVER (ORDER BY dt) AS bal
FROM   tbl9;

Or a MODEL clause:

SELECT dt,
       prin,
       int,
       bal
FROM   (
  SELECT t.*,
         ROWNUM AS rn
  FROM   (SELECT * FROM tbl9 ORDER BY dt) t
)
MODEL
  DIMENSION BY (rn)
  MEASURES (dt, prin, int, 0 AS bal)  
  RULES (
    bal[1]    = prin[1] - int[1],
    bal[rn>1] = prin[CV(rn) - 1] - int[CV(rn)]
  )  

Which, for the sample data, both output:

DT	PRIN	INT	BAL
2023-01-01 00:00:00	100000	1100	98900
2023-02-01 00:00:00	100000	1200	98800
2023-03-01 00:00:00	100000	1500	98500
2023-04-01 00:00:00	100000	2300	97700

fiddle