Dream11: Withdrawals That Missed the 24-Hour Payout Promise

Easy
628.

Dream11: Withdrawals That Missed the 24-Hour Payout Promise

EasyDates

MySQL

Dream11's payments operations team promises a winner's withdrawal within 24 hours and reviews every payout that took longer.

Return every processed withdrawal whose processed_at is more than 24 hours after its requested_at (exactly 24 hours is on time). Return withdrawal_id, user_id and hours_taken, the whole hours between the two timestamps rounded down. Pending and rejected withdrawals are not part of the review. Order by hours_taken descending, then by withdrawal_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: Withdrawal

ColumnTypeValues
withdrawal_idint
user_idint
amountint
requested_atdatetime
processed_atdatetime
statusenumprocessed, pending, rejected

Primary key: withdrawal_id.

processed_at is set when the money left (status processed) or the request was turned down (rejected); it is NULL while pending.

Example

The rows must come back in the order shown.

Withdrawal

withdrawal_iduser_idamountrequested_atprocessed_atstatus
13115002025-05-01 10:00:002025-05-01 18:30:00processed
232200002025-05-01 11:15:002025-05-02 11:15:00processed
3337502025-05-01 12:00:002025-05-02 12:00:30processed
43150002025-05-02 09:00:002025-05-05 14:45:00processed
5343002025-05-02 22:10:00NULLpending
63590002025-05-03 08:00:002025-05-06 08:00:00rejected

Output

withdrawal_iduser_idhours_taken
43177
33324
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: Withdrawals That Missed the 24-Hour Payout Promise

Dream11's payments operations team promises a winner's withdrawal within 24 hours and reviews every payout that took longer.

  • Difficulty: Easy
  • Topics: Dates
  • Dialect: MySQL
  • Problem: #628

Problem statement

Dream11's payments operations team promises a winner's withdrawal within 24 hours and reviews every payout that took longer.

Return every processed withdrawal whose processed_at is more than 24 hours after its requested_at (exactly 24 hours is on time). Return withdrawal_id, user_id and hours_taken, the whole hours between the two timestamps rounded down. Pending and rejected withdrawals are not part of the review. Order by hours_taken descending, then by withdrawal_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: Withdrawal

ColumnType
withdrawal_idint
user_idint
amountint
requested_atdatetime
processed_atdatetime
statusenum(processed, pending, rejected)

Primary key: withdrawal_id.

processed_at is set when the money left (status processed) or the request was turned down (rejected); it is NULL while pending.

Examples

Example 1

Withdrawal

withdrawal_iduser_idamountrequested_atprocessed_atstatus
13115002025-05-01 10:00:002025-05-01 18:30:00processed
232200002025-05-01 11:15:002025-05-02 11:15:00processed
3337502025-05-01 12:00:002025-05-02 12:00:30processed
43150002025-05-02 09:00:002025-05-05 14:45:00processed
5343002025-05-02 22:10:00NULLpending
63590002025-05-03 08:00:002025-05-06 08:00:00rejected

Output

withdrawal_iduser_idhours_taken
43177
33324

How to solve Dream11: Withdrawals That Missed the 24-Hour Payout Promise

The question has two parts: which withdrawals breached the promise, and how late each one was. Both are differences between two timestamps on the same row.

The breach test must be precise at the boundary. TIMESTAMPDIFF(HOUR, …) > 24 would let a payout of 24 hours and 59 minutes through, because whole hours round down to 24. Measuring in seconds — TIMESTAMPDIFF(SECOND, requested_at, processed_at) > 86400 — or comparing processed_at with DATE_ADD(requested_at, INTERVAL 24 HOUR) gets it exactly: exactly 24 hours is on time, one second more is late. The reported hours_taken is the rounded-down hour count, so a payout 24 hours and 30 seconds late shows 24.

The status filter matters because rejected requests also carry a processed_at; pending rows have NULL there, and any comparison with NULL is false anyway. The alternative derives hours as minutes DIV 60, which is the same rounding. One scan of the table.

Reference solution (MySQL)

SELECT withdrawal_id, user_id, TIMESTAMPDIFF(HOUR, requested_at, processed_at) AS hours_taken
FROM Withdrawal
WHERE status = 'processed'
  AND TIMESTAMPDIFF(SECOND, requested_at, processed_at) > 86400
ORDER BY hours_taken DESC, withdrawal_id

Another way

SELECT withdrawal_id, user_id, TIMESTAMPDIFF(MINUTE, requested_at, processed_at) DIV 60 AS hours_taken
FROM Withdrawal
WHERE status = 'processed' AND processed_at > DATE_ADD(requested_at, INTERVAL 24 HOUR)
ORDER BY hours_taken DESC, withdrawal_id

← Dream11: Promo Code Redemptions per Campaign · Dream11: Contests and Prize Pools per Sport in May 2025 →