Dream11: Withdrawals Right After a Deposit With No Play Between
Money that is deposited and taken straight back out without being used to play is a risk signal (for example, a stolen card being cashed out).
- Difficulty: Hard
- Topics: Subqueries, Dates, Joins
- Dialect: MySQL
- Problem: #645
Problem statement
Money that is deposited and taken straight back out without being used to play is a risk signal (for example, a stolen card being cashed out). Dream11's risk team reviews such withdrawals.
Flag a withdrawal when the same user made a deposit at most 48 hours before it (the deposit at or before requested_at, and requested_at no later than exactly 48 hours after the deposit), and the user joined no contest from that deposit's time through the withdrawal's time, both ends included. If several deposits qualify, the withdrawal is still listed once. Return withdrawal_id, user_id and amount (of the withdrawal), ordered by withdrawal_id.
A practice scenario set at Dream11: the tables and data are invented, and this is not a question from, or endorsed by, Dream11.
Tables
Table: Deposit
| Column | Type |
|---|---|
| deposit_id | int |
| user_id | int |
| amount | int |
| deposited_at | datetime |
Primary key: deposit_id.
Successful deposits only.
Table: Withdrawal
| Column | Type |
|---|---|
| withdrawal_id | int |
| user_id | int |
| amount | int |
| requested_at | datetime |
Primary key: withdrawal_id.
Every withdrawal request.
Table: ContestEntry
| Column | Type |
|---|---|
| entry_id | int |
| user_id | int |
| joined_at | datetime |
Primary key: entry_id.
One row per contest entry, with the time it was made.
Examples
Example 1
Deposit
| deposit_id | user_id | amount | deposited_at |
|---|---|---|---|
| 1 | 81 | 5000 | 2025-07-01 10:00:00 |
| 2 | 82 | 2000 | 2025-07-01 12:00:00 |
| 3 | 83 | 1000 | 2025-07-02 09:00:00 |
| 4 | 84 | 3000 | 2025-07-02 10:00:00 |
| 5 | 84 | 3000 | 2025-07-03 20:00:00 |
| 6 | 85 | 1500 | 2025-07-04 08:00:00 |
Withdrawal
| withdrawal_id | user_id | amount | requested_at |
|---|---|---|---|
| 10 | 81 | 4900 | 2025-07-01 16:00:00 |
| 11 | 82 | 2000 | 2025-07-02 10:00:00 |
| 12 | 83 | 1000 | 2025-07-04 09:00:00 |
| 13 | 84 | 2500 | 2025-07-03 22:00:00 |
| 14 | 85 | 1500 | 2025-07-06 08:00:01 |
ContestEntry
| entry_id | user_id | joined_at |
|---|---|---|
| 20 | 82 | 2025-07-01 18:30:00 |
| 21 | 84 | 2025-07-02 11:00:00 |
| 22 | 83 | 2025-07-01 09:00:00 |
Output
| withdrawal_id | user_id | amount |
|---|---|---|
| 10 | 81 | 4900 |
| 12 | 83 | 1000 |
| 13 | 84 | 2500 |
How to solve Dream11: Withdrawals Right After a Deposit With No Play Between
The rule pairs a withdrawal with a deposit and then asks about everything in between, which reads naturally as two nested existence tests. The outer EXISTS looks for a deposit of the same user in the 48 hours up to the withdrawal: d.deposited_at <= w.requested_at AND w.requested_at <= DATE_ADD(d.deposited_at, INTERVAL 48 HOUR). The inner NOT EXISTS checks that no contest entry of that user falls BETWEEN d.deposited_at AND w.requested_at. Because the inner test refers to d, it is evaluated per candidate deposit — a user who played after an early deposit but then deposited again and withdrew immediately is still flagged through the later deposit.
Both boundaries are inclusive by the statement: a withdrawal exactly 48 hours after the deposit qualifies and one second later does not, and an entry at the very second of the deposit counts as play. The outer EXISTS makes the withdrawal appear once however many deposits qualify.
The alternative builds every qualifying (withdrawal, deposit) pair with a join, left-joins the entries inside the window, keeps the pairs with no entry (HAVING COUNT(e.entry_id) = 0) and removes duplicate withdrawals with DISTINCT. With an index on user_id and the time column in each table, both run in a few index range scans per withdrawal.
Reference solution (MySQL)
SELECT w.withdrawal_id, w.user_id, w.amount
FROM Withdrawal w
WHERE EXISTS (
SELECT 1 FROM Deposit d
WHERE d.user_id = w.user_id
AND d.deposited_at <= w.requested_at
AND w.requested_at <= DATE_ADD(d.deposited_at, INTERVAL 48 HOUR)
AND NOT EXISTS (
SELECT 1 FROM ContestEntry e
WHERE e.user_id = w.user_id
AND e.joined_at BETWEEN d.deposited_at AND w.requested_at
)
)
ORDER BY w.withdrawal_idAnother way
SELECT DISTINCT withdrawal_id, user_id, amount FROM (
SELECT w.withdrawal_id, w.user_id, w.amount, d.deposit_id
FROM Withdrawal w
JOIN Deposit d ON d.user_id = w.user_id
AND TIMESTAMPDIFF(SECOND, d.deposited_at, w.requested_at) BETWEEN 0 AND 172800
LEFT JOIN ContestEntry e ON e.user_id = w.user_id
AND e.joined_at >= d.deposited_at AND e.joined_at <= w.requested_at
GROUP BY w.withdrawal_id, w.user_id, w.amount, d.deposit_id
HAVING COUNT(e.entry_id) = 0
) x
ORDER BY withdrawal_id← Dream11: Contest Winners From Player Points With C and VC Boosts · Dream11: Second-Paid-Contest Activation Rate by Signup Month →