Get the difference for a column value by all its previous rows data

All we need is an easy explanation of the problem, so here it is.

Please consider these two tables:

  1. Vault

    • id BIGSERIAL PRIMARY KEY
    • name VARCHAR(30) NOT NULL
  2. Transaction

    • id BIGSERIAL PRIMARY KEY
    • transaction_type VARCHAR(6) NOT NULL — Choice between "credit" or "debit"
    • amount NUMERIC(15,6) NOT NULL
    • vault_id BIGINT REFERENCES Vault (id)

When getting Transaction row/s, I would like to get an extra column for the remaining vault balance after the transaction is done.

Example of Transaction:

id      transaction_type        amount      vault_id        vault_balance
1       credit                  100         1               100 // since there was no prev transaction for this vault
5       credit                  400         1               500
12      debit                   200         1               300
20      credit                  100         1               400

I can add a column to hold the vault balance and use that for the next transaction. But suppose the user deletes a row, for example row id 5 which has credit amount of 400 is deleted. Then its following transactions vault balance should change accordingly. So for row 12 the vault balance would be -100, and row id 20’s vault balance would be 0.

id      transaction_type        amount      vault_id        vault_balance
1       credit                  100         1               100 // since there was no prev transaction for this vault
12      debit                   200         1               -100
20      credit                  100         1               0

The way I see it I have two ways:

  • Add a column to store the vault balance in a transaction, and if there is a change, all of its following transaction’s vault balance should be updated accordingly.
  • Maybe there’s a way to get the difference of credit and debit between all of its previous and the current transaction amount.

But I am hoping there is a much better way? Could you please help me what would be most efficient way to handle such situation?

Update

Link to the Table fiddle.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Something like this, may be?

sum(case transaction_type when 'debit' then -1 else 1 end * amount)
over (partition by vault_id order by id rows unbounded preceding and current row)

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply