Ola: Wallets Topped Up Over ₹10,000 in May 2024

Easy
826.

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

ColumnTypeValues
txn_idint
user_idint
txn_typeenumtopup, ride_payment, refund, cashback
amountint
txn_atdatetime

Primary key: txn_id.

One row per wallet transaction; amount is in rupees and always positive.

Example

The rows must come back in the order shown.

WalletTxn

txn_iduser_idtxn_typeamounttxn_at
111topup60002024-05-02 09:10:00
211topup50002024-05-20 18:00:00
312topup100002024-05-05 11:30:00
412refund5002024-05-06 08:00:00
513topup70002024-04-30 23:59:59
613topup40002024-05-31 23:00:00
714topup90002024-05-12 10:00:00
814topup20002024-06-01 00:00:00
915topup80002024-05-09 12:00:00
1015topup30002024-05-10 12:00:00

Output

user_idmay_topups
1111000
1511000
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.

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

ColumnType
txn_idint
user_idint
txn_typeenum(topup, ride_payment, refund, cashback)
amountint
txn_atdatetime

Primary key: txn_id.

One row per wallet transaction; amount is in rupees and always positive.

Examples

Example 1

WalletTxn

txn_iduser_idtxn_typeamounttxn_at
111topup60002024-05-02 09:10:00
211topup50002024-05-20 18:00:00
312topup100002024-05-05 11:30:00
412refund5002024-05-06 08:00:00
513topup70002024-04-30 23:59:59
613topup40002024-05-31 23:00:00
714topup90002024-05-12 10:00:00
814topup20002024-06-01 00:00:00
915topup80002024-05-09 12:00:00
1015topup30002024-05-10 12:00:00

Output

user_idmay_topups
1111000
1511000

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_id

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

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