Task
You are given account transactions and need to produce a row-by-row balance audit for each account. Write a PostgreSQL query that shows each transaction with the prior balance, the balance change from the previous row, and the running balance after the transaction. The logic must work across records in timestamp order, so this is a good test of how you handle row-dependent calculations.
Schema
| table | column | type | description |
|---|
| account_transactions | transaction_id | INT | Primary key for the transaction |
| account_transactions | account_id | INT | Account identifier |
| account_transactions | transaction_ts | TIMESTAMP | Transaction timestamp |
| account_transactions | transaction_type | VARCHAR(20) | deposit, withdrawal, or adjustment |
| account_transactions | amount | NUMERIC(12,2) | Signed amount applied to the account |
| account_transactions | note | TEXT | Optional transaction note |
Sample data
| transaction_id | account_id | transaction_ts | transaction_type | amount | note |
|---|
| 1 | 101 | 2024-01-01 09:00:00 | deposit | 100.00 | opening deposit |
| 2 | 101 | 2024-01-02 10:00:00 | withdrawal | -25.00 | card payment |
| 3 | 101 | 2024-01-03 08:30:00 | adjustment | 10.00 | fee reversal |
| 4 | 102 | 2024-01-01 11:00:00 | deposit | 200.00 | opening deposit |
| 5 | 102 | 2024-01-02 12:00:00 | withdrawal | -50.00 | bill pay |
| 6 | 102 | 2024-01-04 09:15:00 | withdrawal | -20.00 | atm cash |
| 7 | 103 | 2024-01-02 14:00:00 | deposit | 0.00 | zero-value test |
| 8 | 103 | 2024-01-03 09:00:00 | adjustment | NULL | missing amount |
| 9 | 104 | 2024-01-01 08:00:00 | deposit | 500.00 | opening deposit |
| 10 | 104 | 2024-01-01 08:00:00 | adjustment | -15.00 | same timestamp tie |
| 11 | 105 | 2024-01-05 16:00:00 | withdrawal | -30.00 | no prior deposit |
| 12 | 106 | 2024-01-03 13:00:00 | deposit | 75.00 | late deposit |
Expected output
| account_id | transaction_id | transaction_ts | amount | prior_balance | balance_change | running_balance |
|---|
| 101 | 1 | 2024-01-01 09:00:00 | 100.00 | NULL | NULL | 100.00 |
| 101 | 2 | 2024-01-02 10:00:00 | -25.00 | 100.00 | -125.00 | 75.00 |
| 101 | 3 | 2024-01-03 08:30:00 | 10.00 | 75.00 | -65.00 | 85.00 |
| 102 | 4 | 2024-01-01 11:00:00 | 200.00 | NULL | NULL | 200.00 |
| 102 | 5 | 2024-01-02 12:00:00 | -50.00 | 200.00 | -250.00 | 150.00 |
| 102 | 6 | 2024-01-04 09:15:00 | -20.00 | 150.00 | -170.00 | 130.00 |
| 103 | 7 | 2024-01-02 14:00:00 | 0.00 | NULL | NULL | 0.00 |
| 103 | 8 | 2024-01-03 09:00:00 | NULL | 0.00 | NULL | 0.00 |
| 104 | 9 | 2024-01-01 08:00:00 | 500.00 | NULL | NULL | 500.00 |
| 104 | 10 | 2024-01-01 08:00:00 | -15.00 | 500.00 | -515.00 | 485.00 |
| 105 | 11 | 2024-01-05 16:00:00 | -30.00 | NULL | NULL | -30.00 |
| 106 | 12 | 2024-01-03 13:00:00 | 75.00 | NULL | NULL | 75.00 |
