Skip to content
Advertisement

Calculate account balance history in PostgreSQL

I am trying to get a balance history on the account using SQL. My table in PostgreSQL looks like this:

so the user with id number 2 currently has 40 dollars in his account. I would like to get this result using sql:

Is it possible to do something like this using sql in postgres?

Advertisement

Answer

To get a rolling balance, you can SUM the amounts (up to and including the current row) based on whether the id was the recipient or sender:

Output:

If you want an array, you can use array_agg with the above query as a derived table:

Output:

Demo on dbfiddle

If you want to be more sophisticated and support balances for multiple accounts, you need to split the initial data into account ids, adding when the id is the recipient and subtracting when the sender. You can use CTEs to generate the appropriate data:

Output:

Demo on dbfiddle

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement