1

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.

0

1 Answer 1

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)
1
  • You are a godsend! Today I learned something new (about window functions), and all because of you. Thank you. BTW this was the final statement SELECT *, SUM(CASE WHEN transaction_type = 'debit' THEN -1 ELSE 1 END * amount) OVER (PARTITION BY vault_id ORDER BY id RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) vault_balance FROM transactions;.
    – Msw Tm
    May 17, 2022 at 18:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.