Rapid Repeated UPI Transfers: a Fraud Signal — SQL Problem
A common mule-account pattern is a burst of transfers from one payer to the same payee.
- Difficulty: Hard
- Topics: Window Functions, Dates, Joins
- Dialect: MySQL
- Problem: #66
Problem statement
A common mule-account pattern is a burst of transfers from one payer to the same payee. The fraud engine flags a (payer, payee) pair when there are 3 or more successful transfers between them whose times fall within 10 minutes (600 seconds) of the first of them, inclusive — transfers at 10:00:00, 10:04:00 and 10:10:00 are a burst; 10:00:00, 10:04:00 and 10:10:01 are not. Failed transfers are ignored.
Return payer_vpa, payee_vpa and first_flagged_at — the earliest txn_time that starts such a burst — for every flagged pair. Order the rows by first_flagged_at, then payer_vpa, then payee_vpa.
Tables
Table: UpiTransfer
| Column | Type |
|---|---|
| txn_id | int |
| payer_vpa | varchar |
| payee_vpa | varchar |
| amount | int |
| status | enum(SUCCESS, FAILED) |
| txn_time | datetime |
Primary key: txn_id.
One row per transfer attempt; VPAs are always lower case.
Examples
Example 1
UpiTransfer
| txn_id | payer_vpa | payee_vpa | amount | status | txn_time |
|---|---|---|---|---|---|
| 1 | ravi7@ybl | quickcash@paytm | 9999 | SUCCESS | 2024-10-05 10:00:00 |
| 2 | ravi7@ybl | quickcash@paytm | 9999 | SUCCESS | 2024-10-05 10:04:00 |
| 3 | ravi7@ybl | quickcash@paytm | 9999 | SUCCESS | 2024-10-05 10:10:00 |
| 4 | ravi7@ybl | quickcash@paytm | 9999 | SUCCESS | 2024-10-05 10:12:30 |
| 5 | neha2@oksbi | grocer@okaxis | 640 | SUCCESS | 2024-10-05 11:00:00 |
| 6 | neha2@oksbi | grocer@okaxis | 640 | FAILED | 2024-10-05 11:02:00 |
| 7 | neha2@oksbi | grocer@okaxis | 640 | SUCCESS | 2024-10-05 11:03:00 |
| 8 | neha2@oksbi | grocer@okaxis | 640 | SUCCESS | 2024-10-05 11:10:01 |
| 9 | zara5@ibl | giftcards@ybl | 5000 | SUCCESS | 2024-10-05 09:58:00 |
| 10 | zara5@ibl | giftcards@ybl | 5000 | SUCCESS | 2024-10-05 10:01:00 |
| 11 | zara5@ibl | giftcards@ybl | 5000 | SUCCESS | 2024-10-05 10:01:00 |
Output
| payer_vpa | payee_vpa | first_flagged_at |
|---|---|---|
| zara5@ibl | giftcards@ybl | 2024-10-05 09:58:00 |
| ravi7@ybl | quickcash@paytm | 2024-10-05 10:00:00 |
How to solve Rapid Repeated UPI Transfers: a Fraud Signal
The rule is a sliding window over time, anchored at each transfer: transfer t starts a burst when at least three successful transfers of the same pair (itself included) happen in [t, t + 600 s]. Only the starting points need checking — any burst begins at one of its own transfers.
The reference solution makes that literal with a self join: pair each successful transfer a with every successful transfer b of the same payer and payee whose time is 0 to 600 seconds later (TIMESTAMPDIFF(SECOND, a, b) BETWEEN 0 AND 600), group by a, and keep the groups with COUNT(*) >= 3. The minimum time per pair is the first flag. BETWEEN is inclusive, so a third transfer at exactly 600 s counts and one at 601 s does not; failed attempts are filtered on both sides.
The window alternative is neater and linear after a sort: within each pair, ordered by time, the third transfer from t is LEAD(txn_time, 2), and the burst exists iff that time is at most 600 s after t. Equal timestamps are harmless — the earliest-txn_id transfer at a tied second sees all its peers ahead of it, so the minimum is the same. A correlated count with DATE_ADD(…, INTERVAL 600 SECOND) is the third form.
Reference solution (MySQL)
WITH burst_start AS (
SELECT a.txn_id, a.payer_vpa, a.payee_vpa, a.txn_time
FROM UpiTransfer a
JOIN UpiTransfer b
ON b.payer_vpa = a.payer_vpa AND b.payee_vpa = a.payee_vpa AND b.status = 'SUCCESS'
AND TIMESTAMPDIFF(SECOND, a.txn_time, b.txn_time) BETWEEN 0 AND 600
WHERE a.status = 'SUCCESS'
GROUP BY a.txn_id, a.payer_vpa, a.payee_vpa, a.txn_time
HAVING COUNT(*) >= 3
)
SELECT payer_vpa, payee_vpa, MIN(txn_time) AS first_flagged_at
FROM burst_start
GROUP BY payer_vpa, payee_vpa
ORDER BY first_flagged_at, payer_vpa, payee_vpaAnother way
WITH ok AS (
SELECT payer_vpa, payee_vpa, txn_time,
LEAD(txn_time, 2) OVER (PARTITION BY payer_vpa, payee_vpa ORDER BY txn_time, txn_id) AS third_time
FROM UpiTransfer WHERE status = 'SUCCESS'
)
SELECT payer_vpa, payee_vpa, MIN(txn_time) AS first_flagged_at FROM ok
WHERE third_time IS NOT NULL AND TIMESTAMPDIFF(SECOND, txn_time, third_time) <= 600
GROUP BY payer_vpa, payee_vpa ORDER BY first_flagged_at, payer_vpa, payee_vpaAnother way
SELECT payer_vpa, payee_vpa, MIN(txn_time) AS first_flagged_at FROM UpiTransfer a
WHERE status = 'SUCCESS' AND (
SELECT COUNT(*) FROM UpiTransfer b WHERE b.status = 'SUCCESS' AND b.payer_vpa = a.payer_vpa AND b.payee_vpa = a.payee_vpa
AND b.txn_time >= a.txn_time AND b.txn_time <= DATE_ADD(a.txn_time, INTERVAL 600 SECOND)) >= 3
GROUP BY payer_vpa, payee_vpa ORDER BY first_flagged_at, payer_vpa, payee_vpa← Running Wallet Balance After Every Transaction · Longest Run of Consecutive Missed Loan Instalments →