Dream11: Withdrawals Right After a Deposit With No Play Between

Hard
645.

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). 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

ColumnType
deposit_idint
user_idint
amountint
deposited_atdatetime

Primary key: deposit_id.

Successful deposits only.

Table: Withdrawal

ColumnType
withdrawal_idint
user_idint
amountint
requested_atdatetime

Primary key: withdrawal_id.

Every withdrawal request.

Table: ContestEntry

ColumnType
entry_idint
user_idint
joined_atdatetime

Primary key: entry_id.

One row per contest entry, with the time it was made.

Example

The rows must come back in the order shown.

Deposit

deposit_iduser_idamountdeposited_at
18150002025-07-01 10:00:00
28220002025-07-01 12:00:00
38310002025-07-02 09:00:00
48430002025-07-02 10:00:00
58430002025-07-03 20:00:00
68515002025-07-04 08:00:00

Withdrawal

withdrawal_iduser_idamountrequested_at
108149002025-07-01 16:00:00
118220002025-07-02 10:00:00
128310002025-07-04 09:00:00
138425002025-07-03 22:00:00
148515002025-07-06 08:00:01

ContestEntry

entry_iduser_idjoined_at
20822025-07-01 18:30:00
21842025-07-02 11:00:00
22832025-07-01 09:00:00

Output

withdrawal_iduser_idamount
10814900
12831000
13842500
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.

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

ColumnType
deposit_idint
user_idint
amountint
deposited_atdatetime

Primary key: deposit_id.

Successful deposits only.

Table: Withdrawal

ColumnType
withdrawal_idint
user_idint
amountint
requested_atdatetime

Primary key: withdrawal_id.

Every withdrawal request.

Table: ContestEntry

ColumnType
entry_idint
user_idint
joined_atdatetime

Primary key: entry_id.

One row per contest entry, with the time it was made.

Examples

Example 1

Deposit

deposit_iduser_idamountdeposited_at
18150002025-07-01 10:00:00
28220002025-07-01 12:00:00
38310002025-07-02 09:00:00
48430002025-07-02 10:00:00
58430002025-07-03 20:00:00
68515002025-07-04 08:00:00

Withdrawal

withdrawal_iduser_idamountrequested_at
108149002025-07-01 16:00:00
118220002025-07-02 10:00:00
128310002025-07-04 09:00:00
138425002025-07-03 22:00:00
148515002025-07-06 08:00:01

ContestEntry

entry_iduser_idjoined_at
20822025-07-01 18:30:00
21842025-07-02 11:00:00
22832025-07-01 09:00:00

Output

withdrawal_iduser_idamount
10814900
12831000
13842500

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_id

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