Dream11: Deposits Above a User's Own Monthly Limit

Easy
630.

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. 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

ColumnType
user_idint
usernamevarchar
deposit_limitint

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

ColumnTypeValues
deposit_idint
user_idint
amountint
statusenumsuccess, failed
deposited_atdatetime

Primary key: deposit_id.

Every attempt to add cash to the wallet.

Example

The rows must come back in the order shown.

AppUser

user_idusernamedeposit_limit
1aarav_215000
2diya_072000
3kabir_88NULL
4meera_141000
5rohan_333000

Deposit

deposit_iduser_idamountstatusdeposited_at
1113000success2025-09-02 10:00:00
1212500success2025-09-20 21:00:00
1321500success2025-09-05 12:00:00
1421000failed2025-09-06 12:00:00
15390000success2025-09-07 09:00:00
1641000success2025-09-30 23:50:00
174800success2025-10-01 00:05:00
1852000success2025-09-11 18:00:00
1951500success2025-09-12 18:00:00

Output

usernamedeposit_limitdeposited
aarav_2150005500
rohan_3330003500
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: 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

ColumnType
user_idint
usernamevarchar
deposit_limitint

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

ColumnType
deposit_idint
user_idint
amountint
statusenum(success, failed)
deposited_atdatetime

Primary key: deposit_id.

Every attempt to add cash to the wallet.

Examples

Example 1

AppUser

user_idusernamedeposit_limit
1aarav_215000
2diya_072000
3kabir_88NULL
4meera_141000
5rohan_333000

Deposit

deposit_iduser_idamountstatusdeposited_at
1113000success2025-09-02 10:00:00
1212500success2025-09-20 21:00:00
1321500success2025-09-05 12:00:00
1421000failed2025-09-06 12:00:00
15390000success2025-09-07 09:00:00
1641000success2025-09-30 23:50:00
174800success2025-10-01 00:05:00
1852000success2025-09-11 18:00:00
1951500success2025-09-12 18:00:00

Output

usernamedeposit_limitdeposited
aarav_2150005500
rohan_3330003500

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.username

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