Running Wallet Balance After Every Transaction — SQL Problem
A prepaid wallet starts at zero. Money added, cashback and refunds raise the balance; payments lower it.
- Difficulty: Medium
- Topics: Window Functions
- Dialect: MySQL
- Problem: #65
Problem statement
A prepaid wallet starts at zero. Money added, cashback and refunds raise the balance; payments lower it. Support agents need the balance after each transaction, taking a user's transactions in order of txn_time and, for two at the same second, by txn_id.
Return user_id, txn_id, txn_time and running_balance for every transaction, ordered by user_id, then txn_time, then txn_id.
Tables
Table: WalletTxn
| Column | Type |
|---|---|
| txn_id | int |
| user_id | int |
| txn_time | datetime |
| txn_type | enum(ADD_MONEY, PAYMENT, CASHBACK, REFUND) |
| amount | int |
Primary key: txn_id.
One row per wallet transaction; amount is always positive and txn_type decides its direction.
Examples
Example 1
WalletTxn
| txn_id | user_id | txn_time | txn_type | amount |
|---|---|---|---|---|
| 1 | 7 | 2024-04-01 09:00:00 | ADD_MONEY | 2000 |
| 2 | 7 | 2024-04-01 12:30:00 | PAYMENT | 450 |
| 3 | 9 | 2024-04-01 12:30:00 | ADD_MONEY | 500 |
| 4 | 7 | 2024-04-02 08:15:00 | CASHBACK | 25 |
| 5 | 7 | 2024-04-02 08:15:00 | PAYMENT | 1200 |
| 6 | 9 | 2024-04-03 19:00:00 | PAYMENT | 499 |
| 7 | 7 | 2024-04-01 10:00:00 | REFUND | 100 |
Output
| user_id | txn_id | txn_time | running_balance |
|---|---|---|---|
| 7 | 1 | 2024-04-01 09:00:00 | 2000 |
| 7 | 7 | 2024-04-01 10:00:00 | 2100 |
| 7 | 2 | 2024-04-01 12:30:00 | 1650 |
| 7 | 4 | 2024-04-02 08:15:00 | 1675 |
| 7 | 5 | 2024-04-02 08:15:00 | 475 |
| 9 | 3 | 2024-04-01 12:30:00 | 500 |
| 9 | 6 | 2024-04-03 19:00:00 | 1 |
How to solve Running Wallet Balance After Every Transaction
A running balance is a cumulative sum of signed amounts. The sign comes from a CASE (-amount for a payment, amount otherwise), and the cumulative sum from SUM(...) OVER (PARTITION BY user_id ORDER BY txn_time, txn_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) — the partition restarts the balance for every user.
The frame clause is not decoration. Without it the default frame is RANGE … CURRENT ROW, which includes every peer of the current row in the ordering; if the order were only txn_time, two transactions at the same second would both show the balance after both. Ordering by txn_time, txn_id makes every row unique, and ROWS states the intent explicitly.
Before window functions this was written as a correlated subquery or a self join that sums every earlier-or-equal transaction of the same user, with the same tie rule spelled out as u.txn_time < t.txn_time OR (u.txn_time = t.txn_time AND u.txn_id <= t.txn_id). Those are quadratic per user; the window version is one sort and a single pass.
Reference solution (MySQL)
SELECT user_id, txn_id, txn_time,
SUM(CASE WHEN txn_type = 'PAYMENT' THEN -amount ELSE amount END)
OVER (PARTITION BY user_id ORDER BY txn_time, txn_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_balance
FROM WalletTxn
ORDER BY user_id, txn_time, txn_idAnother way
SELECT t.user_id, t.txn_id, t.txn_time,
(SELECT SUM(IF(u.txn_type = 'PAYMENT', -u.amount, u.amount)) FROM WalletTxn u
WHERE u.user_id = t.user_id AND (u.txn_time < t.txn_time OR (u.txn_time = t.txn_time AND u.txn_id <= t.txn_id))) AS running_balance
FROM WalletTxn t ORDER BY t.user_id, t.txn_time, t.txn_idAnother way
SELECT t.user_id, t.txn_id, t.txn_time, SUM(CASE WHEN u.txn_type = 'PAYMENT' THEN -u.amount ELSE u.amount END) AS running_balance
FROM WalletTxn t JOIN WalletTxn u ON u.user_id = t.user_id
AND (u.txn_time < t.txn_time OR (u.txn_time = t.txn_time AND u.txn_id <= t.txn_id))
GROUP BY t.user_id, t.txn_id, t.txn_time ORDER BY t.user_id, t.txn_time, t.txn_id← Loan Book Asset Classification by Branch · Rapid Repeated UPI Transfers: a Fraud Signal →