SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `Transaction`(id INTEGER PRIMARY KEY, is_credit INTEGER, amount INTEGER);
✓
INSERT INTO `Transaction`(is_credit, amount) VALUES
(1, 100), (1, 50), (0,20),(1, 30), (0,50);
✓
SELECT t.id, 
       t.amount,
       (SELECT SUM(CASE WHEN tt.is_credit = 1 THEN 1 ELSE -1 END * tt.amount) FROM `Transaction` tt WHERE tt.id <= t.id) AS balance 
FROM `Transaction` t
ORDER BY t.id DESC;


id
amount
balance


5
50
110

4
30
160

3
20
130

2
50
150

1
100
100


SELECT t.id, 
       t.amount,
       SUM(CASE WHEN tt.is_credit = 1 THEN 1 ELSE -1 END * tt.amount) AS balance 
FROM `Transaction` t INNER JOIN `Transaction` tt
ON tt.id <= t.id
GROUP BY t.id
ORDER BY t.id DESC;


id
amount
balance


5
50
110

4
30
160

3
20
130

2
50
150

1
100
100