Dream11: Depositors Who Never Joined a Contest

Easy
624.

Dream11: Depositors Who Never Joined a Contest

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

ColumnType
user_idint
usernamevarchar
signup_datedate

Primary key: user_id.

One row per registered account.

Table: Deposit

ColumnTypeValues
deposit_idint
user_idint
amountint
statusenumsuccess, failed, pending

Primary key: deposit_id.

Every attempt to add cash to the wallet, whatever its outcome.

Table: ContestEntry

ColumnType
entry_idint
user_idint
contest_idint

Primary key: entry_id.

One row per team a user entered into a contest.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

AppUser

user_idusernamesignup_date
1aarav_212025-03-02
2diya_072025-03-04
3kabir_882025-03-05
4meera_142025-03-09
5rohan_332025-03-11
6zara_502025-03-12

Deposit

deposit_iduser_idamountstatus
111500success
122100success
132200success
1431000failed
155300pending
165300success

ContestEntry

entry_iduser_idcontest_id
211900
224901
231902

Output

user_idusername
2diya_07
5rohan_33
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: 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

ColumnType
user_idint
usernamevarchar
signup_datedate

Primary key: user_id.

One row per registered account.

Table: Deposit

ColumnType
deposit_idint
user_idint
amountint
statusenum(success, failed, pending)

Primary key: deposit_id.

Every attempt to add cash to the wallet, whatever its outcome.

Table: ContestEntry

ColumnType
entry_idint
user_idint
contest_idint

Primary key: entry_id.

One row per team a user entered into a contest.

Examples

Example 1

AppUser

user_idusernamesignup_date
1aarav_212025-03-02
2diya_072025-03-04
3kabir_882025-03-05
4meera_142025-03-09
5rohan_332025-03-11
6zara_502025-03-12

Deposit

deposit_iduser_idamountstatus
111500success
122100success
132200success
1431000failed
155300pending
165300success

ContestEntry

entry_iduser_idcontest_id
211900
224901
231902

Output

user_idusername
2diya_07
5rohan_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 NULL

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