Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit



SELECT 1


create table transactions (
  id int not null primary key,
  user_id int not null,
  debit_acc_id int not null,
  credit_acc_id int not null,
  amount int not null
);

insert into transactions values 
(1, 1, 1, 2, 500), (2, 1, 1, 3, 600), (3, 1, 3, 1, 200);

CREATE TABLE



INSERT 0 3


select id, user_id, 
       case when user_id = debit_acc_id then amount else 0 end as debit,
       case when user_id = credit_acc_id then amount else 0 end as credit,
       sum(case when user_id = debit_acc_id then amount else 0 end) over w 
       - sum(case when user_id = credit_acc_id then amount else 0 end) over w as balance
  from transactions
 where user_id = 1
window w as (partition by user_id order by id)
 order by user_id, id;


id
user_id
debit
credit
balance


1
1
500
0
500

2
1
600
0
1100

3
1
0
200
900



SELECT 3