Swiggy: Swiggy Money Debits That Overdrew the Wallet

Medium
965.

Swiggy: Swiggy Money Debits That Overdrew the Wallet

MediumWindow Functions

MySQL

Swiggy Money is the in-app wallet: refunds and top-ups credit it, payments debit it. A reconciliation job found that a race in the payment service let some debits go through without enough balance, and the payments team wants every one of them.

Each customer's wallet starts at 0. Process the customer's transactions in order of txn_at, and for transactions at the same moment in order of txn_id; the balance after a transaction is the running total of credits minus debits up to and including it. Return txn_id, customer_id and balance_after for every debit that left the balance below zero. Order the rows by txn_id.

A practice scenario set at Swiggy: the tables and data are invented, and this is not a question from, or endorsed by, Swiggy.

Tables

Table: WalletTxn

ColumnTypeValues
txn_idint
customer_idint
txn_atdatetime
kindenumcredit, debit
amountint

Primary key: txn_id.

One row per wallet movement; amount is always positive, in rupees, and kind says which way it moved.

Example

The rows must come back in the order shown.

WalletTxn

txn_idcustomer_idtxn_atkindamount
11012025-09-01 10:00:00credit500
21012025-09-01 12:00:00debit300
31012025-09-02 09:00:00debit250
41012025-09-02 09:00:00credit100
51012025-09-03 08:00:00debit80
61022025-09-01 09:00:00debit40
71022025-09-01 09:30:00credit40
81032025-09-01 11:00:00credit200
91032025-09-01 11:05:00debit200

Output

txn_idcustomer_idbalance_after
3101-50
5101-30
6102-40
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.

Swiggy: Swiggy Money Debits That Overdrew the Wallet

Swiggy Money is the in-app wallet: refunds and top-ups credit it, payments debit it.

  • Difficulty: Medium
  • Topics: Window Functions
  • Dialect: MySQL
  • Problem: #965

Problem statement

Swiggy Money is the in-app wallet: refunds and top-ups credit it, payments debit it. A reconciliation job found that a race in the payment service let some debits go through without enough balance, and the payments team wants every one of them.

Each customer's wallet starts at 0. Process the customer's transactions in order of txn_at, and for transactions at the same moment in order of txn_id; the balance after a transaction is the running total of credits minus debits up to and including it. Return txn_id, customer_id and balance_after for every debit that left the balance below zero. Order the rows by txn_id.

A practice scenario set at Swiggy: the tables and data are invented, and this is not a question from, or endorsed by, Swiggy.

Tables

Table: WalletTxn

ColumnType
txn_idint
customer_idint
txn_atdatetime
kindenum(credit, debit)
amountint

Primary key: txn_id.

One row per wallet movement; amount is always positive, in rupees, and kind says which way it moved.

Examples

Example 1

WalletTxn

txn_idcustomer_idtxn_atkindamount
11012025-09-01 10:00:00credit500
21012025-09-01 12:00:00debit300
31012025-09-02 09:00:00debit250
41012025-09-02 09:00:00credit100
51012025-09-03 08:00:00debit80
61022025-09-01 09:00:00debit40
71022025-09-01 09:30:00credit40
81032025-09-01 11:00:00credit200
91032025-09-01 11:05:00debit200

Output

txn_idcustomer_idbalance_after
3101-50
5101-30
6102-40

How to solve Swiggy: Swiggy Money Debits That Overdrew the Wallet

The balance after a transaction is a running sum of signed amounts, so a window function computes it in one pass: SUM(CASE WHEN kind = 'credit' THEN amount ELSE -amount END) over each customer's transactions ordered by txn_at, txn_id. The frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW makes "up to and including this row" exact. With the default RANGE frame, two transactions with the same txn_at and txn_id order would still be fine here because txn_id is unique, but spelling out ROWS avoids surprises when the order key has ties.

The tie-breaker matters: in the example a debit and a credit share 09:00, and the statement processes the lower id first, so the debit lands at −50 before the credit restores the balance. Only debits are reported — a credit can never push a balance below zero — and a balance of exactly 0 is not overdrawn.

The alternative recomputes the balance for each debit with a correlated sum over all earlier-or-equal transactions of that customer, ordered by the same (time, id) pair. It is quadratic per customer; the window version sorts each customer's transactions once.

Reference solution (MySQL)

WITH running AS (
  SELECT txn_id, customer_id, kind,
         SUM(CASE WHEN kind = 'credit' THEN amount ELSE -amount END)
           OVER (PARTITION BY customer_id ORDER BY txn_at, txn_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance_after
  FROM WalletTxn
)
SELECT txn_id, customer_id, balance_after
FROM running
WHERE kind = 'debit' AND balance_after < 0
ORDER BY txn_id

Another way

SELECT t.txn_id, t.customer_id,
       (SELECT SUM(IF(p.kind = 'credit', p.amount, -p.amount)) FROM WalletTxn p
        WHERE p.customer_id = t.customer_id
          AND (p.txn_at < t.txn_at OR (p.txn_at = t.txn_at AND p.txn_id <= t.txn_id))) AS balance_after
FROM WalletTxn t
WHERE t.kind = 'debit'
  AND (SELECT SUM(IF(p.kind = 'credit', p.amount, -p.amount)) FROM WalletTxn p
       WHERE p.customer_id = t.customer_id
         AND (p.txn_at < t.txn_at OR (p.txn_at = t.txn_at AND p.txn_id <= t.txn_id))) < 0
ORDER BY t.txn_id

← Swiggy: Partner Top-Up to the Shift Earnings Guarantee · Swiggy: Third Delivered Order of Each New Customer →