logo

Back to questions

Monthly Merchant Balance

Hard

Say you have access to all the transactions for a given merchant account. Write a query to print the cumulative balance of the merchant account at the end of each day, with the total balance reset back to zero at the end of the month. Output the transaction date and cumulative balance.

Table:

Column NameType
transaction_idinteger
typestring ('deposit', 'withdrawal')
amountdecimal
transaction_datetimestamp

Example Input:

transaction_idtypeamounttransaction_date
19153deposit65.9007/10/2022 10:00:00
53151deposit178.5507/08/2022 10:00:00
29776withdrawal25.9007/08/2022 10:00:00
16461withdrawal45.9907/08/2022 10:00:00
77134deposit32.6007/10/2022 10:00:00

Example Output:

transaction_datebalance
07/08/2022 12:00:00106.66
07/10/2022 12:00:00205.16

To get cumulative balance of 106.66 on 07/08/2022 12:00:00, we take the deposit of 178.55 and minus against two withdrawals 25.90 and 45.99.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.