How to calculate rolling transactions balance

Table: transactions

 id client_id transaction_type amount   
 1  1         credit             1000 
 2  1         debit               100 
 3  1         credit              500 
 4  1         debit               300 
 5  2         debit              1000 
 6  2         credit             1200 
 7  3         debit              1000 
 8  3         credit             1000 
 9  4         debit              1000

Question: How we can get each client balance after each transaction?

Solution: In PostgreSQL, Microsoft SQL Server and MySQL since version 8.0 we can use WINDOW FUNCTIONS

select 
  transaction_id,
  client_id,
  case when action_type='credit' then action_amount else 0 end credit,
  case when action_type='debit' then action_amount else 0 end debit,
  sum(case when action_type='credit' then action_amount else -action_amount end) over(partition by client_id order by transaction_id) balance
from transactions
order by client_id, transaction_id;

Use next links for test the above solution: https://sqlize.online/sql/psql12/8fd9d6aea07d1186a99dd1856cb55447/