Rider Cash-in-Hand Between Deposits — SQL Problem
Riders collect cash on cash-on-delivery orders and hand it in at a hub.
- Difficulty: Hard
- Topics: Window Functions, Conditional Logic
- Dialect: MySQL
- Problem: #171
Problem statement
Riders collect cash on cash-on-delivery orders and hand it in at a hub. A cod_collected entry adds its amount to the rider's cash in hand; a deposit hands in all the cash and resets it to 0. Entries apply in entry_at order. For safety a rider should never carry more than ₹2,000.
For every rider with at least one entry, return rider_id, max_cash_in_hand (the most cash they held after any entry; 0 if they never held any) and limit_breaches (the number of cod_collected entries after which the cash in hand was more than ₹2,000). Order the rows by rider_id.
Tables
Table: CashLedger
| Column | Type |
|---|---|
| entry_id | int |
| rider_id | int |
| entry_at | datetime |
| entry_type | enum(cod_collected, deposit) |
| amount | int |
Primary key: entry_id.
One row per cash event, in rupees. A rider never has two entries at the same instant. For a deposit, amount is what the hub counted and does not affect the rule.
Examples
Example 1
CashLedger
| entry_id | rider_id | entry_at | entry_type | amount |
|---|---|---|---|---|
| 1 | 31 | 2024-12-02 12:10:00 | cod_collected | 650 |
| 2 | 31 | 2024-12-02 13:00:00 | cod_collected | 900 |
| 3 | 31 | 2024-12-02 13:40:00 | cod_collected | 520 |
| 4 | 31 | 2024-12-02 14:30:00 | cod_collected | 300 |
| 5 | 31 | 2024-12-02 15:00:00 | deposit | 2370 |
| 6 | 31 | 2024-12-02 19:15:00 | cod_collected | 1200 |
| 7 | 32 | 2024-12-02 12:30:00 | cod_collected | 2000 |
| 8 | 32 | 2024-12-02 13:30:00 | deposit | 2000 |
| 9 | 32 | 2024-12-02 20:00:00 | cod_collected | 450 |
| 10 | 33 | 2024-12-02 10:00:00 | deposit | 0 |
Output
| rider_id | max_cash_in_hand | limit_breaches |
|---|---|---|
| 31 | 2370 | 2 |
| 32 | 2000 | 0 |
| 33 | 0 | 0 |
How to solve Rider Cash-in-Hand Between Deposits
A plain running SUM would keep growing across deposits; the cash in hand is a running total that resets at every deposit. The standard trick is to turn the resets into a grouping key. A running count of deposits — SUM(CASE WHEN entry_type = 'deposit' THEN 1 ELSE 0 END) OVER (PARTITION BY rider_id ORDER BY entry_at …) — is 0 before the first deposit, 1 from the first deposit up to the second, and so on. Every row between two deposits carries the same cycle number, and a deposit opens its own cycle.
Now a second window, partitioned by rider and cycle, sums only the collected amounts in time order: that is the cash after each entry. A deposit row adds 0 to an empty cycle, so its cash is 0, which is the reset. The rest is a group per rider: MAX(cash) (0 for a rider who only deposited), and a conditional count of collection rows whose cash is above 2,000 — exactly 2,000 (rider 32) is not a breach.
The order must be by entry_at, not entry_id — the ids were not issued in time order. The correlated alternative finds, for each entry, the latest deposit at or before it and sums the collections after that deposit; it is quadratic in a rider's entries, where the two windows are one sort.
Reference solution (MySQL)
WITH cycles AS (
SELECT rider_id, entry_at, entry_type, amount,
SUM(CASE WHEN entry_type = 'deposit' THEN 1 ELSE 0 END)
OVER (PARTITION BY rider_id ORDER BY entry_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cycle
FROM CashLedger
), balances AS (
SELECT rider_id, entry_type,
SUM(CASE WHEN entry_type = 'cod_collected' THEN amount ELSE 0 END)
OVER (PARTITION BY rider_id, cycle ORDER BY entry_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cash
FROM cycles
)
SELECT rider_id,
MAX(cash) AS max_cash_in_hand,
SUM(CASE WHEN entry_type = 'cod_collected' AND cash > 2000 THEN 1 ELSE 0 END) AS limit_breaches
FROM balances
GROUP BY rider_id
ORDER BY rider_idAnother way
WITH balances AS (
SELECT e.rider_id, e.entry_type,
(SELECT COALESCE(SUM(c.amount), 0) FROM CashLedger c
WHERE c.rider_id = e.rider_id AND c.entry_type = 'cod_collected' AND c.entry_at <= e.entry_at
AND c.entry_at > COALESCE((SELECT MAX(d.entry_at) FROM CashLedger d
WHERE d.rider_id = e.rider_id AND d.entry_type = 'deposit' AND d.entry_at <= e.entry_at),
'1900-01-01 00:00:00')) AS cash
FROM CashLedger e
)
SELECT rider_id, MAX(cash) AS max_cash_in_hand,
COUNT(CASE WHEN entry_type = 'cod_collected' AND cash > 2000 THEN 1 END) AS limit_breaches
FROM balances GROUP BY rider_id ORDER BY rider_id← Food App Checkout Funnel, Step by Step · Top Two Restaurants per Cuisine by Repeat-Customer Rate →