Running Wallet Balance After Every Transaction

Medium
65.

Running Wallet Balance After Every Transaction

MediumWindow Functions

MySQL

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

ColumnTypeValues
txn_idint
user_idint
txn_timedatetime
txn_typeenumADD_MONEY, PAYMENT, CASHBACK, REFUND
amountint

Primary key: txn_id.

One row per wallet transaction; amount is always positive and txn_type decides its direction.

Example

The rows must come back in the order shown.

WalletTxn

txn_iduser_idtxn_timetxn_typeamount
172024-04-01 09:00:00ADD_MONEY2000
272024-04-01 12:30:00PAYMENT450
392024-04-01 12:30:00ADD_MONEY500
472024-04-02 08:15:00CASHBACK25
572024-04-02 08:15:00PAYMENT1200
692024-04-03 19:00:00PAYMENT499
772024-04-01 10:00:00REFUND100

Output

user_idtxn_idtxn_timerunning_balance
712024-04-01 09:00:002000
772024-04-01 10:00:002100
722024-04-01 12:30:001650
742024-04-02 08:15:001675
752024-04-02 08:15:00475
932024-04-01 12:30:00500
962024-04-03 19:00:001
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
txn_idint
user_idint
txn_timedatetime
txn_typeenum(ADD_MONEY, PAYMENT, CASHBACK, REFUND)
amountint

Primary key: txn_id.

One row per wallet transaction; amount is always positive and txn_type decides its direction.

Examples

Example 1

WalletTxn

txn_iduser_idtxn_timetxn_typeamount
172024-04-01 09:00:00ADD_MONEY2000
272024-04-01 12:30:00PAYMENT450
392024-04-01 12:30:00ADD_MONEY500
472024-04-02 08:15:00CASHBACK25
572024-04-02 08:15:00PAYMENT1200
692024-04-03 19:00:00PAYMENT499
772024-04-01 10:00:00REFUND100

Output

user_idtxn_idtxn_timerunning_balance
712024-04-01 09:00:002000
772024-04-01 10:00:002100
722024-04-01 12:30:001650
742024-04-02 08:15:001675
752024-04-02 08:15:00475
932024-04-01 12:30:00500
962024-04-03 19:00:001

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_id

Another 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_id

Another 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 →