PhonePe: Users Paying Three New Payees Within One Hour
A common account-takeover pattern is a burst of transfers to people the account has never paid before. The fraud team's velocity rule looks for that burst.
- Difficulty: Hard
- Topics: Window Functions, Subqueries, Dates
- Dialect: MySQL
- Problem: #891
Problem statement
A common account-takeover pattern is a burst of transfers to people the account has never paid before. The fraud team's velocity rule looks for that burst.
A new-payee transfer is a sender's first successful transfer to a given payee_vpa (failed attempts do not count, later transfers to the same payee are not new). Flag every sender with three or more new-payee transfers inside some span of 60 minutes, both ends included — the third may be sent exactly 60 minutes after the first. Return sender_id and first_alert_at, the sent_at of the earliest new-payee transfer that starts such a span. Return the rows in any order.
A practice scenario set at PhonePe: the tables and data are invented, and this is not a question from, or endorsed by, PhonePe.
Tables
Table: P2pTransfer
| Column | Type |
|---|---|
| transfer_id | int |
| sender_id | int |
| payee_vpa | varchar |
| amount | int |
| status | enum(success, failed) |
| sent_at | datetime |
Primary key: transfer_id.
One row per peer-to-peer transfer attempt; times are whole minutes.
Examples
Example 1
P2pTransfer
| transfer_id | sender_id | payee_vpa | amount | status | sent_at |
|---|---|---|---|---|---|
| 1 | 91 | asha@ybl | 2000 | success | 2025-05-12 10:00:00 |
| 2 | 91 | vikram.k7@ibl | 4500 | success | 2025-05-12 10:20:00 |
| 3 | 91 | asha@ybl | 500 | success | 2025-05-12 10:30:00 |
| 4 | 91 | 9812345678@axl | 9000 | success | 2025-05-12 11:00:00 |
| 5 | 92 | farhan@ybl | 1500 | success | 2025-05-12 09:00:00 |
| 6 | 92 | pooja22@ybl | 1500 | success | 2025-05-12 09:30:00 |
| 7 | 92 | karan@ibl | 1500 | success | 2025-05-12 10:01:00 |
| 8 | 92 | simran@axl | 1500 | success | 2025-05-12 10:15:00 |
| 9 | 93 | dev@ybl | 300 | failed | 2025-05-12 08:00:00 |
| 10 | 93 | ira.n3@ibl | 300 | success | 2025-05-12 08:10:00 |
| 11 | 93 | harsh@ybl | 300 | success | 2025-05-12 08:40:00 |
| 12 | 93 | dev@ybl | 300 | success | 2025-05-12 09:30:00 |
Output
| sender_id | first_alert_at |
|---|---|
| 91 | 2025-05-12 10:00:00 |
| 92 | 2025-05-12 09:30:00 |
How to solve PhonePe: Users Paying Three New Payees Within One Hour
First turn the raw transfers into the events the rule is about. MIN(sent_at) over successful transfers, grouped by sender and payee, is the moment each payee became a payee — a later transfer to the same person is not new, and a failed attempt (sender 93's 08:00 try to dev@ybl) does not make the payee known, so the first success at 09:30 is the new-payee event.
Any qualifying hour can be slid forward until it starts on a new-payee event, so it is enough to try each event as a start. The self-join counts the events in [start, start + 60 minutes] for the same sender; DATE_ADD(…, INTERVAL 60 MINUTE) builds the end, and the inclusive <= admits sender 91's third payee at exactly 11:00. Sender 92's first three are 61 minutes apart, but the span starting at 09:30 holds three, so the alert time is 09:30. The earliest qualifying start per sender is MIN(first_at).
The window alternative avoids the self-join: in time order, the span starting at an event holds three events exactly when the event two places later, LEAD(first_at, 2), is within the hour. That is a sort per sender instead of a quadratic join, and the right choice on real volumes. The third query does the count with a correlated subquery.
Reference solution (MySQL)
WITH firsts AS (
SELECT sender_id, payee_vpa, MIN(sent_at) AS first_at
FROM P2pTransfer
WHERE status = 'success'
GROUP BY sender_id, payee_vpa
), spans AS (
SELECT a.sender_id, a.payee_vpa, a.first_at, COUNT(*) AS new_payees
FROM firsts a
JOIN firsts b ON b.sender_id = a.sender_id
AND b.first_at >= a.first_at
AND b.first_at <= DATE_ADD(a.first_at, INTERVAL 60 MINUTE)
GROUP BY a.sender_id, a.payee_vpa, a.first_at
)
SELECT sender_id, MIN(first_at) AS first_alert_at
FROM spans
WHERE new_payees >= 3
GROUP BY sender_idAnother way
WITH firsts AS (SELECT sender_id, payee_vpa, MIN(sent_at) AS first_at FROM P2pTransfer WHERE status = 'success' GROUP BY sender_id, payee_vpa),
ahead AS (SELECT sender_id, first_at, LEAD(first_at, 2) OVER (PARTITION BY sender_id ORDER BY first_at) AS third_at FROM firsts)
SELECT sender_id, MIN(first_at) AS first_alert_at FROM ahead
WHERE third_at <= DATE_ADD(first_at, INTERVAL 60 MINUTE)
GROUP BY sender_idAnother way
WITH firsts AS (SELECT sender_id, payee_vpa, MIN(sent_at) AS first_at FROM P2pTransfer WHERE status = 'success' GROUP BY sender_id, payee_vpa)
SELECT a.sender_id, MIN(a.first_at) AS first_alert_at FROM firsts a
WHERE (SELECT COUNT(*) FROM firsts b WHERE b.sender_id = a.sender_id AND b.first_at BETWEEN a.first_at AND DATE_ADD(a.first_at, INTERVAL 60 MINUTE)) >= 3
GROUP BY a.sender_id← PhonePe: Rewards Campaign Redemption Report on 1 April · PhonePe: Failed UPI Payments Recovered by a Retry, per Bank →