PhonePe: Three-Way Circular Transfers Within 24 Hours

Hard
897.

PhonePe: Three-Way Circular Transfers Within 24 Hours

Money that goes round in a circle — A pays B, B pays C, C pays A — can be used to fake activity or launder funds. The fraud-analytics team scans for three-person rings that close quickly.

A ring is three successful transfers A → B, B → C and C → A between three different users, each sent strictly after the previous one, with the last sent no more than 24 hours after the first. Report each ring once as user_1, user_2, user_3: user_1 is the smallest user id of the three, and the others follow the direction the money moved (user_1 paid user_2, user_2 paid user_3). A ring that occurs several times is listed once. Order the rows by user_1, user_2, user_3.

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: Transfer

ColumnTypeValues
transfer_idint
from_userint
to_userint
amountint
statusenumsuccess, failed
sent_atdatetime

Primary key: transfer_id.

One row per peer-to-peer transfer; from_user and to_user are always different.

Example

The rows must come back in the order shown.

Transfer

transfer_idfrom_userto_useramountstatussent_at
11011025000success2025-09-01 10:00:00
21021034900success2025-09-01 14:00:00
31031014800success2025-09-02 10:00:00
41041052000success2025-09-01 09:00:00
51051062000success2025-09-01 09:30:00
61061042000success2025-09-02 09:01:00
7107108700success2025-09-03 08:00:00
8108105700success2025-09-03 08:05:00
9105107700failed2025-09-03 08:10:00
101061099000success2025-09-04 10:00:00
2 more rows

Output

user_1user_2user_3
101102103
104106109
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: Three-Way Circular Transfers Within 24 Hours

Money that goes round in a circle — A pays B, B pays C, C pays A — can be used to fake activity or launder funds.

  • Difficulty: Hard
  • Topics: Joins, Dates, Conditional Logic
  • Dialect: MySQL
  • Problem: #897

Problem statement

Money that goes round in a circle — A pays B, B pays C, C pays A — can be used to fake activity or launder funds. The fraud-analytics team scans for three-person rings that close quickly.

A ring is three successful transfers A → B, B → C and C → A between three different users, each sent strictly after the previous one, with the last sent no more than 24 hours after the first. Report each ring once as user_1, user_2, user_3: user_1 is the smallest user id of the three, and the others follow the direction the money moved (user_1 paid user_2, user_2 paid user_3). A ring that occurs several times is listed once. Order the rows by user_1, user_2, user_3.

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: Transfer

ColumnType
transfer_idint
from_userint
to_userint
amountint
statusenum(success, failed)
sent_atdatetime

Primary key: transfer_id.

One row per peer-to-peer transfer; from_user and to_user are always different.

Examples

Example 1

Transfer

transfer_idfrom_userto_useramountstatussent_at
11011025000success2025-09-01 10:00:00
21021034900success2025-09-01 14:00:00
31031014800success2025-09-02 10:00:00
41041052000success2025-09-01 09:00:00
51051062000success2025-09-01 09:30:00
61061042000success2025-09-02 09:01:00
7107108700success2025-09-03 08:00:00
8108105700success2025-09-03 08:05:00
9105107700failed2025-09-03 08:10:00
101061099000success2025-09-04 10:00:00
111091049000success2025-09-04 11:00:00
121041069000success2025-09-04 12:00:00

Output

user_1user_2user_3
101102103
104106109

How to solve PhonePe: Three-Way Circular Transfers Within 24 Hours

A ring is a path of length three in the transfer graph that returns to its start, so the query is a three-way self-join of the successful transfers: t2 starts where t1 ended, t3 starts where t2 ended and ends where t1 started. The join conditions also carry the time rules — t2 strictly after t1, t3 strictly after t2, and t3 no later than DATE_ADD(t1.sent_at, INTERVAL 24 HOUR). The extra t2.to_user <> t1.from_user excludes a back-and-forth A → B → A, which is not three people.

The same ring can be discovered with any member as the first sender — in the example the 104/106/109 ring starts with 106 paying 109 — so each match is rotated into one canonical form: the smallest id first, keeping the direction of the money. The CASE expressions pick the rotation; DISTINCT then lists a ring once however many times it ran. Ring 104 → 105 → 106 → 104 closed 24 hours and one minute after it opened, so it is not reported; exactly 24 hours (101 → 102 → 103) is.

The alternative writes the rotation as three UNIONed rows per ring and keeps the one whose first user is the smallest, letting UNION remove duplicates. The self-join is the costly part; with an index on (from_user, sent_at) each step only reads one user's transfers.

Reference solution (MySQL)

WITH s AS (
  SELECT from_user, to_user, sent_at FROM Transfer WHERE status = 'success'
), ring AS (
  SELECT t1.from_user AS a, t1.to_user AS b, t2.to_user AS c
  FROM s t1
  JOIN s t2 ON t2.from_user = t1.to_user AND t2.sent_at > t1.sent_at
  JOIN s t3 ON t3.from_user = t2.to_user AND t3.to_user = t1.from_user
           AND t3.sent_at > t2.sent_at
           AND t3.sent_at <= DATE_ADD(t1.sent_at, INTERVAL 24 HOUR)
  WHERE t2.to_user <> t1.from_user
)
SELECT DISTINCT
       CASE WHEN a < b AND a < c THEN a WHEN b < c THEN b ELSE c END AS user_1,
       CASE WHEN a < b AND a < c THEN b WHEN b < c THEN c ELSE a END AS user_2,
       CASE WHEN a < b AND a < c THEN c WHEN b < c THEN a ELSE b END AS user_3
FROM ring
ORDER BY user_1, user_2, user_3

Another way

WITH s AS (SELECT from_user, to_user, sent_at FROM Transfer WHERE status = 'success'),
ring AS (
  SELECT t1.from_user AS a, t2.from_user AS b, t3.from_user AS c
  FROM s t1, s t2, s t3
  WHERE t1.to_user = t2.from_user AND t2.to_user = t3.from_user AND t3.to_user = t1.from_user
    AND t3.from_user <> t1.from_user
    AND t1.sent_at < t2.sent_at AND t2.sent_at < t3.sent_at
    AND TIMESTAMPDIFF(SECOND, t1.sent_at, t3.sent_at) <= 86400
),
rotations AS (
  SELECT a AS user_1, b AS user_2, c AS user_3 FROM ring
  UNION SELECT b, c, a FROM ring
  UNION SELECT c, a, b FROM ring
)
SELECT user_1, user_2, user_3 FROM rotations
WHERE user_1 < user_2 AND user_1 < user_3
ORDER BY user_1, user_2, user_3

← PhonePe: Merchants Earning Half Their Takings From Three Customers · Razorpay: Payment Links That Lapsed Without Payment →