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
| Column | Type |
|---|---|
| user_id | int |
| submitted_at | datetime |
| approved_at | datetime |
| status | enum(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
| Column | Type |
|---|---|
| withdrawal_id | int |
| user_id | int |
| amount | int |
| requested_at | datetime |
Primary key: withdrawal_id.
Every withdrawal request the app accepted.
Examples
Example 1
KycCheck
| user_id | submitted_at | approved_at | status |
|---|---|---|---|
| 1 | 2025-06-01 10:00:00 | 2025-06-02 12:00:00 | approved |
| 2 | 2025-06-01 11:00:00 | NULL | pending |
| 3 | 2025-06-03 09:00:00 | 2025-06-03 15:00:00 | approved |
| 4 | 2025-06-04 09:00:00 | NULL | rejected |
Withdrawal
| withdrawal_id | user_id | amount | requested_at |
|---|---|---|---|
| 10 | 1 | 500 | 2025-06-02 11:59:59 |
| 11 | 1 | 700 | 2025-06-02 12:00:00 |
| 12 | 2 | 1200 | 2025-06-05 10:00:00 |
| 13 | 3 | 900 | 2025-06-04 10:00:00 |
| 14 | 5 | 300 | 2025-06-05 18:00:00 |
| 15 | 2 | 300 | 2025-06-06 10:00:00 |
| 16 | 1 | 1000 | 2025-06-01 20:00:00 |
Output
| user_id | early_requests | early_amount |
|---|---|---|
| 1 | 2 | 1500 |
| 2 | 2 | 1500 |
| 5 | 1 | 300 |
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_idAnother 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 →