Paytm: Merchant Days When QR Collections Fell by More Than Half
A sudden fall in a shop's QR collections often means a broken Soundbox or a scanner taped over, so the merchant-success team calls the shop.
- Difficulty: Medium
- Topics: Window Functions, Aggregation, Dates
- Dialect: MySQL
- Problem: #865
Problem statement
A sudden fall in a shop's QR collections often means a broken Soundbox or a scanner taped over, so the merchant-success team calls the shop.
A merchant's collection on a day is the sum of its successful payments that day; only days with at least one successful payment count as collection days. Compare each collection day with the merchant's previous collection day (not necessarily the calendar day before). Return the days whose collection is strictly less than half of the previous one, with merchant_id, collection_date, collection and previous_collection. A merchant's first collection day is never returned. Return the rows ordered by merchant_id, then collection_date.
A practice scenario set at Paytm: the tables and data are invented, and this is not a question from, or endorsed by, Paytm.
Tables
Table: QrPayment
| Column | Type |
|---|---|
| payment_id | int |
| merchant_id | int |
| paid_at | datetime |
| amount | int |
| status | enum(success, failed) |
Primary key: payment_id.
One row per customer payment to a merchant's QR code.
Examples
Example 1
QrPayment
| payment_id | merchant_id | paid_at | amount | status |
|---|---|---|---|---|
| 1 | 1 | 2025-03-01 09:00:00 | 1000 | success |
| 2 | 1 | 2025-03-01 18:00:00 | 600 | success |
| 3 | 1 | 2025-03-02 11:00:00 | 900 | success |
| 4 | 1 | 2025-03-04 10:30:00 | 400 | success |
| 5 | 1 | 2025-03-05 12:00:00 | 200 | success |
| 6 | 2 | 2025-03-01 13:00:00 | 3000 | success |
| 7 | 2 | 2025-03-02 13:00:00 | 5000 | failed |
| 8 | 2 | 2025-03-03 13:00:00 | 1000 | success |
| 9 | 3 | 2025-03-02 08:00:00 | 500 | success |
Output
| merchant_id | collection_date | collection | previous_collection |
|---|---|---|---|
| 1 | 2025-03-04 | 400 | 900 |
| 2 | 2025-03-03 | 1000 | 3000 |
How to solve Paytm: Merchant Days When QR Collections Fell by More Than Half
Start by turning payments into collection days: keep successful payments, group by merchant and calendar day (CAST(paid_at AS DATE)), and sum. A day whose only payment failed — merchant 2 on 2 March — produces no row, so it can never be anyone's "previous day".
Then compare each day with the one before it in the same merchant's list. LAG(collection) OVER (PARTITION BY merchant_id ORDER BY collection_date) reads the previous collection day's total, however many calendar days back it is — merchant 1's 4 March is compared with 2 March. The test collection * 2 < previous_collection is "strictly less than half" in integers: 200 against 400 is exactly half and is not reported. On a merchant's first day LAG returns NULL, the comparison is unknown, and the row is filtered out without a special case.
The alternative finds the previous day with a correlated MAX(collection_date) below the current one and joins back to its total. It is quadratic per merchant; the window version is one sort.
Reference solution (MySQL)
WITH daily AS (
SELECT merchant_id, CAST(paid_at AS DATE) AS collection_date, SUM(amount) AS collection
FROM QrPayment
WHERE status = 'success'
GROUP BY merchant_id, CAST(paid_at AS DATE)
), compared AS (
SELECT merchant_id, collection_date, collection,
LAG(collection) OVER (PARTITION BY merchant_id ORDER BY collection_date) AS previous_collection
FROM daily
)
SELECT merchant_id, collection_date, collection, previous_collection
FROM compared
WHERE collection * 2 < previous_collection
ORDER BY merchant_id, collection_dateAnother way
WITH daily AS (SELECT merchant_id, LEFT(paid_at, 10) AS collection_date, SUM(amount) AS collection FROM QrPayment WHERE status = 'success' GROUP BY merchant_id, LEFT(paid_at, 10))
SELECT cur.merchant_id, cur.collection_date, cur.collection, prev.collection AS previous_collection
FROM daily cur
JOIN daily prev ON prev.merchant_id = cur.merchant_id
AND prev.collection_date = (SELECT MAX(x.collection_date) FROM daily x WHERE x.merchant_id = cur.merchant_id AND x.collection_date < cur.collection_date)
WHERE cur.collection < prev.collection / 2
ORDER BY cur.merchant_id, cur.collection_date← Paytm: Cashback Paid Above the Per-User Campaign Cap · Paytm: Gold Buyers Whose Weighted Cost Beat the Simple Average →