Dream11: Deposits Above a User's Own Monthly Limit
Dream11 lets users set a responsible-play limit on how much they can deposit in a calendar month.
- Difficulty: Easy
- Topics: Joins, Aggregation, Dates
- Dialect: MySQL
- Problem: #630
Problem statement
Dream11 lets users set a responsible-play limit on how much they can deposit in a calendar month. After a payment-gateway incident, the responsible-play team wants to find users whose successful deposits in September 2025 went past the limit they had set.
Return username, deposit_limit and deposited (the total of the user's successful deposits in September 2025) for every user whose deposited is greater than their limit. Users with no limit (NULL) are never in the list. Order by how far over the limit they went (deposited - deposit_limit) descending, then by username.
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 |
| deposit_limit | int |
Primary key: user_id.
deposit_limit is the monthly cap in rupees the user chose, or NULL if they never set one. Usernames are unique.
Table: Deposit
| Column | Type |
|---|---|
| deposit_id | int |
| user_id | int |
| amount | int |
| status | enum(success, failed) |
| deposited_at | datetime |
Primary key: deposit_id.
Every attempt to add cash to the wallet.
Examples
Example 1
AppUser
| user_id | username | deposit_limit |
|---|---|---|
| 1 | aarav_21 | 5000 |
| 2 | diya_07 | 2000 |
| 3 | kabir_88 | NULL |
| 4 | meera_14 | 1000 |
| 5 | rohan_33 | 3000 |
Deposit
| deposit_id | user_id | amount | status | deposited_at |
|---|---|---|---|---|
| 11 | 1 | 3000 | success | 2025-09-02 10:00:00 |
| 12 | 1 | 2500 | success | 2025-09-20 21:00:00 |
| 13 | 2 | 1500 | success | 2025-09-05 12:00:00 |
| 14 | 2 | 1000 | failed | 2025-09-06 12:00:00 |
| 15 | 3 | 90000 | success | 2025-09-07 09:00:00 |
| 16 | 4 | 1000 | success | 2025-09-30 23:50:00 |
| 17 | 4 | 800 | success | 2025-10-01 00:05:00 |
| 18 | 5 | 2000 | success | 2025-09-11 18:00:00 |
| 19 | 5 | 1500 | success | 2025-09-12 18:00:00 |
Output
| username | deposit_limit | deposited |
|---|---|---|
| aarav_21 | 5000 | 5500 |
| rohan_33 | 3000 | 3500 |
How to solve Dream11: Deposits Above a User's Own Monthly Limit
Each user's total is a sum over their deposits, and the threshold is a value on the user's own row, so the query joins AppUser to Deposit, keeps only the successful deposits made in September, groups by user and compares the group's total with that user's limit.
Failed deposits never reached the wallet, so they are filtered in WHERE before summing. The month is a half-open range on deposited_at: a deposit at 23:50 on 30 September counts, one at 00:05 on 1 October does not. Because deposit_limit is part of the GROUP BY, it can be used in HAVING SUM(d.amount) > u.deposit_limit. A user with no limit has NULL there; x > NULL is never true, so they would drop out anyway — the explicit IS NOT NULL just says so.
The alternative totals the deposits per user in a derived table and compares outside the aggregation. Ordering by the excess puts the biggest breaches first, with the username breaking ties.
Reference solution (MySQL)
SELECT u.username, u.deposit_limit, SUM(d.amount) AS deposited
FROM AppUser u
JOIN Deposit d ON d.user_id = u.user_id
WHERE d.status = 'success'
AND d.deposited_at >= '2025-09-01 00:00:00' AND d.deposited_at < '2025-10-01 00:00:00'
AND u.deposit_limit IS NOT NULL
GROUP BY u.user_id, u.username, u.deposit_limit
HAVING SUM(d.amount) > u.deposit_limit
ORDER BY deposited - u.deposit_limit DESC, u.usernameAnother way
SELECT u.username, u.deposit_limit, s.deposited
FROM AppUser u
JOIN (SELECT user_id, SUM(amount) AS deposited FROM Deposit
WHERE status = 'success' AND YEAR(deposited_at) = 2025 AND MONTH(deposited_at) = 9
GROUP BY user_id) s ON s.user_id = u.user_id
WHERE s.deposited > u.deposit_limit
ORDER BY s.deposited - u.deposit_limit DESC, u.username← Dream11: Contests and Prize Pools per Sport in May 2025 · Dream11: Selection Percentage of Every Player per Match →