Dream11: Withdrawal Requests Made Before KYC Approval

Medium
638.

Dream11: Withdrawal Requests Made Before KYC Approval

Winnings can only be withdrawn after a user's identity check (KYC) is approved. The compliance team at Dream11 audits whether the app ever accepted a withdrawal request too early.

A withdrawal is early if the user had no approved KYC at requested_at: they have no KYC row, their KYC was never approved (approved_at is NULL), or the request came strictly before approved_at (a request at the exact approval second is fine). For every user with at least one early request, return user_id, early_requests and early_amount (their total). Order by early_amount descending, then by user_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: KycCheck

ColumnTypeValues
user_idint
submitted_atdatetime
approved_atdatetime
statusenumapproved, pending, rejected

Primary key: user_id.

At most one KYC record per user. approved_at is set only when status is approved.

Table: Withdrawal

ColumnType
withdrawal_idint
user_idint
amountint
requested_atdatetime

Primary key: withdrawal_id.

Every withdrawal request the app accepted.

Example

The rows must come back in the order shown.

KycCheck

user_idsubmitted_atapproved_atstatus
12025-06-01 10:00:002025-06-02 12:00:00approved
22025-06-01 11:00:00NULLpending
32025-06-03 09:00:002025-06-03 15:00:00approved
42025-06-04 09:00:00NULLrejected

Withdrawal

withdrawal_iduser_idamountrequested_at
1015002025-06-02 11:59:59
1117002025-06-02 12:00:00
12212002025-06-05 10:00:00
1339002025-06-04 10:00:00
1453002025-06-05 18:00:00
1523002025-06-06 10:00:00
16110002025-06-01 20:00:00

Output

user_idearly_requestsearly_amount
121500
221500
51300
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: Withdrawal Requests Made Before KYC Approval

Winnings can only be withdrawn after a user's identity check (KYC) is approved.

  • Difficulty: Medium
  • Topics: Joins, Aggregation, Dates
  • Dialect: MySQL
  • Problem: #638

Problem statement

Winnings can only be withdrawn after a user's identity check (KYC) is approved. The compliance team at Dream11 audits whether the app ever accepted a withdrawal request too early.

A withdrawal is early if the user had no approved KYC at requested_at: they have no KYC row, their KYC was never approved (approved_at is NULL), or the request came strictly before approved_at (a request at the exact approval second is fine). For every user with at least one early request, return user_id, early_requests and early_amount (their total). Order by early_amount descending, then by user_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: KycCheck

ColumnType
user_idint
submitted_atdatetime
approved_atdatetime
statusenum(approved, pending, rejected)

Primary key: user_id.

At most one KYC record per user. approved_at is set only when status is approved.

Table: Withdrawal

ColumnType
withdrawal_idint
user_idint
amountint
requested_atdatetime

Primary key: withdrawal_id.

Every withdrawal request the app accepted.

Examples

Example 1

KycCheck

user_idsubmitted_atapproved_atstatus
12025-06-01 10:00:002025-06-02 12:00:00approved
22025-06-01 11:00:00NULLpending
32025-06-03 09:00:002025-06-03 15:00:00approved
42025-06-04 09:00:00NULLrejected

Withdrawal

withdrawal_iduser_idamountrequested_at
1015002025-06-02 11:59:59
1117002025-06-02 12:00:00
12212002025-06-05 10:00:00
1339002025-06-04 10:00:00
1453002025-06-05 18:00:00
1523002025-06-06 10:00:00
16110002025-06-01 20:00:00

Output

user_idearly_requestsearly_amount
121500
221500
51300

How to solve Dream11: Withdrawal Requests Made Before KYC Approval

A withdrawal is legitimate only when an approval exists at or before the request. Everything else is early, and "everything else" has three shapes: no KYC record, a record that was never approved, and an approval that came later than the request.

A LEFT JOIN from Withdrawal to KycCheck keeps every request; for the first two shapes k.approved_at is NULL, so k.approved_at IS NULL OR w.requested_at < k.approved_at covers all three in one condition. Without the IS NULL branch, the comparison with NULL would be unknown and those requests — the worst ones — would vanish from the audit. The comparison is strict, so a request at the approval second passes. Grouping by user then counts and totals the early requests.

The alternative states the rule in its positive form, as an anti join: keep the request when no KYC row has approved_at <= requested_at. That handles missing rows and NULL approvals without any special case, since neither can satisfy the comparison. Both are one pass with an index probe per request.

Reference solution (MySQL)

SELECT w.user_id, COUNT(*) AS early_requests, SUM(w.amount) AS early_amount
FROM Withdrawal w
LEFT JOIN KycCheck k ON k.user_id = w.user_id
WHERE k.approved_at IS NULL OR w.requested_at < k.approved_at
GROUP BY w.user_id
ORDER BY early_amount DESC, w.user_id

Another way

SELECT user_id, COUNT(*) AS early_requests, SUM(amount) AS early_amount
FROM Withdrawal w
WHERE NOT EXISTS (SELECT 1 FROM KycCheck k WHERE k.user_id = w.user_id AND k.approved_at <= w.requested_at)
GROUP BY user_id
ORDER BY early_amount DESC, user_id

← Dream11: Private Leagues Cancelled Below Their Minimum Size · Dream11: Each Player's Form Over Their Last Three Matches →