Dream11: Depositors Who Never Joined a Contest — SQL Problem
Dream11's growth team wants to nudge people who added money to their wallet but have not yet played: they get a reminder that a contest is waiting.
- Difficulty: Easy
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #624
Problem statement
Dream11's growth team wants to nudge people who added money to their wallet but have not yet played: they get a reminder that a contest is waiting.
Return every user with at least one successful deposit and no contest entry at all (free or paid). A failed or pending deposit does not count as having deposited. Return user_id and username, one row per user, in any order.
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: AppUser
| Column | Type |
|---|---|
| user_id | int |
| username | varchar |
| signup_date | date |
Primary key: user_id.
One row per registered account.
Table: Deposit
| Column | Type |
|---|---|
| deposit_id | int |
| user_id | int |
| amount | int |
| status | enum(success, failed, pending) |
Primary key: deposit_id.
Every attempt to add cash to the wallet, whatever its outcome.
Table: ContestEntry
| Column | Type |
|---|---|
| entry_id | int |
| user_id | int |
| contest_id | int |
Primary key: entry_id.
One row per team a user entered into a contest.
Examples
Example 1
AppUser
| user_id | username | signup_date |
|---|---|---|
| 1 | aarav_21 | 2025-03-02 |
| 2 | diya_07 | 2025-03-04 |
| 3 | kabir_88 | 2025-03-05 |
| 4 | meera_14 | 2025-03-09 |
| 5 | rohan_33 | 2025-03-11 |
| 6 | zara_50 | 2025-03-12 |
Deposit
| deposit_id | user_id | amount | status |
|---|---|---|---|
| 11 | 1 | 500 | success |
| 12 | 2 | 100 | success |
| 13 | 2 | 200 | success |
| 14 | 3 | 1000 | failed |
| 15 | 5 | 300 | pending |
| 16 | 5 | 300 | success |
ContestEntry
| entry_id | user_id | contest_id |
|---|---|---|
| 21 | 1 | 900 |
| 22 | 4 | 901 |
| 23 | 1 | 902 |
Output
| user_id | username |
|---|---|
| 2 | diya_07 |
| 5 | rohan_33 |
How to solve Dream11: Depositors Who Never Joined a Contest
Each user is tested against two other tables, and neither test needs a value from them — only whether a matching row exists. That is exactly what EXISTS and NOT EXISTS express: keep the user if some successful deposit belongs to them, and if no contest entry belongs to them.
The status filter sits inside the deposit subquery, so a user whose only deposits failed or are still pending is not a depositor. Because EXISTS stops at the first match, a user with several deposits is still one row.
The first alternative joins to the successful deposits and then left-joins the entries, keeping the rows where no entry matched (e.entry_id IS NULL); the inner join repeats a user once per deposit, which DISTINCT folds back. The second uses IN and NOT IN, safe here because ContestEntry.user_id is never NULL — with a nullable column NOT IN would return nothing at all. With indexes on user_id in both child tables every version is a pair of index probes per user.
Reference solution (MySQL)
SELECT u.user_id, u.username
FROM AppUser u
WHERE EXISTS (SELECT 1 FROM Deposit d WHERE d.user_id = u.user_id AND d.status = 'success')
AND NOT EXISTS (SELECT 1 FROM ContestEntry e WHERE e.user_id = u.user_id)Another way
SELECT DISTINCT u.user_id, u.username
FROM AppUser u
JOIN Deposit d ON d.user_id = u.user_id AND d.status = 'success'
LEFT JOIN ContestEntry e ON e.user_id = u.user_id
WHERE e.entry_id IS NULLAnother way
SELECT user_id, username FROM AppUser WHERE user_id IN (SELECT user_id FROM Deposit WHERE status = 'success') AND user_id NOT IN (SELECT user_id FROM ContestEntry)← Dream11: Paid Public Contests Still Under Half Full · Dream11: Gross Winnings, Tax Withheld and Net Payout per User →