PhonePe: Users Paying Three New Payees Within One Hour

Hard
891.

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.

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

ColumnTypeValues
transfer_idint
sender_idint
payee_vpavarchar
amountint
statusenumsuccess, failed
sent_atdatetime

Primary key: transfer_id.

One row per peer-to-peer transfer attempt; times are whole minutes.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

P2pTransfer

transfer_idsender_idpayee_vpaamountstatussent_at
191asha@ybl2000success2025-05-12 10:00:00
291vikram.k7@ibl4500success2025-05-12 10:20:00
391asha@ybl500success2025-05-12 10:30:00
4919812345678@axl9000success2025-05-12 11:00:00
592farhan@ybl1500success2025-05-12 09:00:00
692pooja22@ybl1500success2025-05-12 09:30:00
792karan@ibl1500success2025-05-12 10:01:00
892simran@axl1500success2025-05-12 10:15:00
993dev@ybl300failed2025-05-12 08:00:00
1093ira.n3@ibl300success2025-05-12 08:10:00
2 more rows

Output

sender_idfirst_alert_at
912025-05-12 10:00:00
922025-05-12 09:30: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.

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

ColumnType
transfer_idint
sender_idint
payee_vpavarchar
amountint
statusenum(success, failed)
sent_atdatetime

Primary key: transfer_id.

One row per peer-to-peer transfer attempt; times are whole minutes.

Examples

Example 1

P2pTransfer

transfer_idsender_idpayee_vpaamountstatussent_at
191asha@ybl2000success2025-05-12 10:00:00
291vikram.k7@ibl4500success2025-05-12 10:20:00
391asha@ybl500success2025-05-12 10:30:00
4919812345678@axl9000success2025-05-12 11:00:00
592farhan@ybl1500success2025-05-12 09:00:00
692pooja22@ybl1500success2025-05-12 09:30:00
792karan@ibl1500success2025-05-12 10:01:00
892simran@axl1500success2025-05-12 10:15:00
993dev@ybl300failed2025-05-12 08:00:00
1093ira.n3@ibl300success2025-05-12 08:10:00
1193harsh@ybl300success2025-05-12 08:40:00
1293dev@ybl300success2025-05-12 09:30:00

Output

sender_idfirst_alert_at
912025-05-12 10:00:00
922025-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_id

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

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