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
| Column | Type |
|---|---|
| transfer_id | int |
| from_user | int |
| to_user | int |
| amount | int |
| status | enum(success, failed) |
| sent_at | datetime |
Primary key: transfer_id.
One row per peer-to-peer transfer; from_user and to_user are always different.
Examples
Example 1
Transfer
| transfer_id | from_user | to_user | amount | status | sent_at |
|---|---|---|---|---|---|
| 1 | 101 | 102 | 5000 | success | 2025-09-01 10:00:00 |
| 2 | 102 | 103 | 4900 | success | 2025-09-01 14:00:00 |
| 3 | 103 | 101 | 4800 | success | 2025-09-02 10:00:00 |
| 4 | 104 | 105 | 2000 | success | 2025-09-01 09:00:00 |
| 5 | 105 | 106 | 2000 | success | 2025-09-01 09:30:00 |
| 6 | 106 | 104 | 2000 | success | 2025-09-02 09:01:00 |
| 7 | 107 | 108 | 700 | success | 2025-09-03 08:00:00 |
| 8 | 108 | 105 | 700 | success | 2025-09-03 08:05:00 |
| 9 | 105 | 107 | 700 | failed | 2025-09-03 08:10:00 |
| 10 | 106 | 109 | 9000 | success | 2025-09-04 10:00:00 |
| 11 | 109 | 104 | 9000 | success | 2025-09-04 11:00:00 |
| 12 | 104 | 106 | 9000 | success | 2025-09-04 12:00:00 |
Output
| user_1 | user_2 | user_3 |
|---|---|---|
| 101 | 102 | 103 |
| 104 | 106 | 109 |
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_3Another 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 →