Rider Cash-in-Hand Between Deposits

Hard
171.

Rider Cash-in-Hand Between Deposits

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

ColumnTypeValues
entry_idint
rider_idint
entry_atdatetime
entry_typeenumcod_collected, deposit
amountint

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.

Example

The rows must come back in the order shown.

CashLedger

entry_idrider_identry_atentry_typeamount
1312024-12-02 12:10:00cod_collected650
2312024-12-02 13:00:00cod_collected900
3312024-12-02 13:40:00cod_collected520
4312024-12-02 14:30:00cod_collected300
5312024-12-02 15:00:00deposit2370
6312024-12-02 19:15:00cod_collected1200
7322024-12-02 12:30:00cod_collected2000
8322024-12-02 13:30:00deposit2000
9322024-12-02 20:00:00cod_collected450
10332024-12-02 10:00:00deposit0

Output

rider_idmax_cash_in_handlimit_breaches
3123702
3220000
3300
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.

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

ColumnType
entry_idint
rider_idint
entry_atdatetime
entry_typeenum(cod_collected, deposit)
amountint

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_idrider_identry_atentry_typeamount
1312024-12-02 12:10:00cod_collected650
2312024-12-02 13:00:00cod_collected900
3312024-12-02 13:40:00cod_collected520
4312024-12-02 14:30:00cod_collected300
5312024-12-02 15:00:00deposit2370
6312024-12-02 19:15:00cod_collected1200
7322024-12-02 12:30:00cod_collected2000
8322024-12-02 13:30:00deposit2000
9322024-12-02 20:00:00cod_collected450
10332024-12-02 10:00:00deposit0

Output

rider_idmax_cash_in_handlimit_breaches
3123702
3220000
3300

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_id

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