SQLite 3.39

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

CREATE TABLE transaction_table (`_id` INTEGER, `description` VARCHAR(18), `debit` REAL, `credit` REAL, `curr_id` INTEGER, `cus_id` INTEGER);
✓
INSERT INTO transaction_table (`_id`, `description`, `debit`, `credit`, `curr_id`, `cus_id`) VALUES
  ('1', 'cr for customer 1', '0.0', '30.0', '1', '1'),
  ('2', 'cr for customer 1', '0.0', '500.0', '1', '1'),
  ('3', 'dbt for customer 1', '70.0', '0.0', '1', '1'),
  ('4', 'cr for customer 2', '0.0', '600.0', '1', '2'),
  ('5', 'dat for customer 2', '100.0', '0.0', '1', '2'),
  ('6', 'dat for customer 2', '300.0', '0.0', '1', '2');
✓
SELECT _id, description, debit, credit,
       SUM(debit - credit) OVER (PARTITION BY curr_id, cus_id ORDER BY _id) blnc,
       curr_id, cus_id 
FROM transaction_table; 


_id
description
debit
credit
blnc
curr_id
cus_id


1
cr for customer 1
0
30
-30
1
1

2
cr for customer 1
0
500
-530
1
1

3
dbt for customer 1
70
0
-460
1
1

4
cr for customer 2
0
600
-600
1
2

5
dat for customer 2
100
0
-500
1
2

6
dat for customer 2
300
0
-200
1
2


SELECT t1._id, t1.description, t1.debit, t1.credit,
       SUM(t2.debit - t2.credit) blnc,
       t1.curr_id, t1.cus_id
FROM transaction_table t1 INNER JOIN transaction_table t2
ON t2.curr_id = t1.curr_id AND t2.cus_id = t1.cus_id AND t2._id <= t1._id
GROUP BY t1._id;


_id
description
debit
credit
blnc
curr_id
cus_id


1
cr for customer 1
0
30
-30
1
1

2
cr for customer 1
0
500
-530
1
1

3
dbt for customer 1
70
0
-460
1
1

4
cr for customer 2
0
600
-600
1
2

5
dat for customer 2
100
0
-500
1
2

6
dat for customer 2
300
0
-200
1
2

_id	description	debit	credit	blnc	curr_id	cus_id
1	cr for customer 1	0	30	-30	1	1
2	cr for customer 1	0	500	-530	1	1
3	dbt for customer 1	70	0	-460	1	1
4	cr for customer 2	0	600	-600	1	2
5	dat for customer 2	100	0	-500	1	2
6	dat for customer 2	300	0	-200	1	2