Ola: Wallets Topped Up Over ₹10,000 in May 2024
Wallets on minimum KYC may be loaded with at most ₹10,000 in a calendar month, and the payments compliance team at Ola checks each month that no wallet…
- Difficulty: Easy
- Topics: Aggregation, Dates
- Dialect: MySQL
- Problem: #826
Problem statement
Wallets on minimum KYC may be loaded with at most ₹10,000 in a calendar month, and the payments compliance team at Ola checks each month that no wallet slipped past the limit.
Return every user_id whose top-ups in May 2024 add up to more than 10000, with that total as may_topups. Ride payments, refunds and cashback are not loads and do not count; a wallet at exactly 10000 is within the limit. Order the rows by may_topups from highest to lowest, then by user_id.
A practice scenario set at Ola: the tables and data are invented, and this is not a question from, or endorsed by, Ola.
Tables
Table: WalletTxn
| Column | Type |
|---|---|
| txn_id | int |
| user_id | int |
| txn_type | enum(topup, ride_payment, refund, cashback) |
| amount | int |
| txn_at | datetime |
Primary key: txn_id.
One row per wallet transaction; amount is in rupees and always positive.
Examples
Example 1
WalletTxn
| txn_id | user_id | txn_type | amount | txn_at |
|---|---|---|---|---|
| 1 | 11 | topup | 6000 | 2024-05-02 09:10:00 |
| 2 | 11 | topup | 5000 | 2024-05-20 18:00:00 |
| 3 | 12 | topup | 10000 | 2024-05-05 11:30:00 |
| 4 | 12 | refund | 500 | 2024-05-06 08:00:00 |
| 5 | 13 | topup | 7000 | 2024-04-30 23:59:59 |
| 6 | 13 | topup | 4000 | 2024-05-31 23:00:00 |
| 7 | 14 | topup | 9000 | 2024-05-12 10:00:00 |
| 8 | 14 | topup | 2000 | 2024-06-01 00:00:00 |
| 9 | 15 | topup | 8000 | 2024-05-09 12:00:00 |
| 10 | 15 | topup | 3000 | 2024-05-10 12:00:00 |
Output
| user_id | may_topups |
|---|---|
| 11 | 11000 |
| 15 | 11000 |
How to solve Ola: Wallets Topped Up Over ₹10,000 in May 2024
The check is a total per wallet, so it is a GROUP BY user_id with SUM(amount). Two filters act at different stages. Which rows count — top-ups only, and only those dated in May — is a row filter in WHERE, applied before grouping. Which wallets are reported — those whose total is over 10000 — depends on the sum, so it goes in HAVING, after grouping.
The month is written as a half-open range: on or after 2024-05-01 and before 2024-06-01. That includes a top-up at 23:59:59 on 31 May and excludes one at midnight on 1 June, without depending on how many days the month has. The comparison is strict, so a wallet loaded to exactly ₹10,000 is not flagged. Ties on the total are ordered by user_id.
The alternatives filter the month with YEAR/MONTH or DATE_FORMAT, and one moves the type and month test into a conditional SUM over all rows. The range form is the one that can use an index on txn_at.
Reference solution (MySQL)
SELECT user_id, SUM(amount) AS may_topups
FROM WalletTxn
WHERE txn_type = 'topup'
AND txn_at >= '2024-05-01' AND txn_at < '2024-06-01'
GROUP BY user_id
HAVING SUM(amount) > 10000
ORDER BY may_topups DESC, user_idAnother way
SELECT user_id, may_topups FROM (SELECT user_id, SUM(amount) AS may_topups FROM WalletTxn WHERE txn_type = 'topup' AND YEAR(txn_at) = 2024 AND MONTH(txn_at) = 5 GROUP BY user_id) t WHERE may_topups > 10000 ORDER BY may_topups DESC, user_idAnother way
SELECT user_id, SUM(CASE WHEN txn_type = 'topup' AND DATE_FORMAT(txn_at, '%Y-%m') = '2024-05' THEN amount ELSE 0 END) AS may_topups FROM WalletTxn GROUP BY user_id HAVING may_topups > 10000 ORDER BY may_topups DESC, user_id← Ola: Completed Rentals Bookings per Hourly Package · Ola: Driver Licences Expiring Within 30 Days →