Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tab
AS
SELECT 'a' AS txn_id, 	1 AS amount
UNION ALL SELECT 'b', 	2
UNION ALL SELECT 'c', 	-4
UNION ALL SELECT 'd',	2
UNION ALL SELECT 'e', 	-1
UNION ALL SELECT 'f', 	2
UNION ALL SELECT 'h', 	-4

SELECT 7


SELECT *
FROM tab
ORDER BY txn_id


txn_id
amount


a
1

b
2

c
-4

d
2

e
-1

f
2

h
-4



SELECT 7


WITH RECURSIVE cte_r AS (
  SELECT t.*, ROW_NUMBER() OVER(ORDER BY t.txn_id) AS rn FROM tab t
), cte AS (
  SELECT rn,
         txn_id,
         amount, 
         CASE WHEN amount <= 0 THEN 0 ELSE amount END AS total,
         CASE WHEN amount <= 0 THEN 1 ELSE 0 END AS overage_total
  FROM cte_r
  WHERE rn = 1
  UNION ALL
  SELECT cte_r.rn,
         cte_r.txn_id,
         cte_r.amount,
         CASE WHEN cte.total + cte_r.amount <= 0 THEN 0 
                        ELSE cte.total + cte_r.amount 
                   END AS total,
         cte.overage_total + CASE WHEN cte.total + cte_r.amount <= 0 THEN 1 ELSE 0 END AS overage_total
  FROM cte
  JOIN cte_r
    ON cte.rn = cte_r.rn-1
)
SELECT txn_id, amount, total,overage_total
FROM cte
ORDER BY rn;


txn_id
amount
total
overage_total


a
1
1
0

b
2
3
0

c
-4
0
1

d
2
2
1

e
-1
1
1

f
2
3
1

h
-4
0
2



SELECT 7