Rapid Repeated UPI Transfers: a Fraud Signal

Hard
66.

Rapid Repeated UPI Transfers: a Fraud Signal

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

ColumnTypeValues
txn_idint
payer_vpavarchar
payee_vpavarchar
amountint
statusenumSUCCESS, FAILED
txn_timedatetime

Primary key: txn_id.

One row per transfer attempt; VPAs are always lower case.

Example

The rows must come back in the order shown.

UpiTransfer

txn_idpayer_vpapayee_vpaamountstatustxn_time
1ravi7@yblquickcash@paytm9999SUCCESS2024-10-05 10:00:00
2ravi7@yblquickcash@paytm9999SUCCESS2024-10-05 10:04:00
3ravi7@yblquickcash@paytm9999SUCCESS2024-10-05 10:10:00
4ravi7@yblquickcash@paytm9999SUCCESS2024-10-05 10:12:30
5neha2@oksbigrocer@okaxis640SUCCESS2024-10-05 11:00:00
6neha2@oksbigrocer@okaxis640FAILED2024-10-05 11:02:00
7neha2@oksbigrocer@okaxis640SUCCESS2024-10-05 11:03:00
8neha2@oksbigrocer@okaxis640SUCCESS2024-10-05 11:10:01
9zara5@iblgiftcards@ybl5000SUCCESS2024-10-05 09:58:00
10zara5@iblgiftcards@ybl5000SUCCESS2024-10-05 10:01:00
1 more row

Output

payer_vpapayee_vpafirst_flagged_at
zara5@iblgiftcards@ybl2024-10-05 09:58:00
ravi7@yblquickcash@paytm2024-10-05 10:00:00
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.

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

ColumnType
txn_idint
payer_vpavarchar
payee_vpavarchar
amountint
statusenum(SUCCESS, FAILED)
txn_timedatetime

Primary key: txn_id.

One row per transfer attempt; VPAs are always lower case.

Examples

Example 1

UpiTransfer

txn_idpayer_vpapayee_vpaamountstatustxn_time
1ravi7@yblquickcash@paytm9999SUCCESS2024-10-05 10:00:00
2ravi7@yblquickcash@paytm9999SUCCESS2024-10-05 10:04:00
3ravi7@yblquickcash@paytm9999SUCCESS2024-10-05 10:10:00
4ravi7@yblquickcash@paytm9999SUCCESS2024-10-05 10:12:30
5neha2@oksbigrocer@okaxis640SUCCESS2024-10-05 11:00:00
6neha2@oksbigrocer@okaxis640FAILED2024-10-05 11:02:00
7neha2@oksbigrocer@okaxis640SUCCESS2024-10-05 11:03:00
8neha2@oksbigrocer@okaxis640SUCCESS2024-10-05 11:10:01
9zara5@iblgiftcards@ybl5000SUCCESS2024-10-05 09:58:00
10zara5@iblgiftcards@ybl5000SUCCESS2024-10-05 10:01:00
11zara5@iblgiftcards@ybl5000SUCCESS2024-10-05 10:01:00

Output

payer_vpapayee_vpafirst_flagged_at
zara5@iblgiftcards@ybl2024-10-05 09:58:00
ravi7@yblquickcash@paytm2024-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_vpa

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

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