Ola: Wallet Cashback That Was Not Fully Credited
In August 2024 a promotion promised 10% cashback, capped at ₹50, on every ride paid with the in-app wallet.
- Difficulty: Medium
- Topics: Joins, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #836
Problem statement
In August 2024 a promotion promised 10% cashback, capped at ₹50, on every ride paid with the in-app wallet. Support tickets say some cashback never arrived, so the payments team reconciles what was due against what was credited.
A payment qualifies when its method is ola_money and paid_at is in August 2024. Its cashback due is amount DIV 10 (whole rupees, rounded down), but never more than 50. Credits may arrive in several parts. Return every qualifying payment whose credited total is less than what was due, with payment_id, user_id, due, credited (0 when nothing arrived) and shortfall (due − credited), ordered by payment_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: RidePayment
| Column | Type |
|---|---|
| payment_id | int |
| user_id | int |
| method | enum(ola_money, upi, cash, card) |
| amount | int |
| paid_at | datetime |
Primary key: payment_id.
One row per ride payment, in whole rupees.
Table: CashbackCredit
| Column | Type |
|---|---|
| credit_id | int |
| payment_id | int |
| amount | int |
| credited_at | datetime |
Primary key: credit_id.
One row per cashback credit; a payment can have zero, one or several.
Examples
Example 1
RidePayment
| payment_id | user_id | method | amount | paid_at |
|---|---|---|---|---|
| 1 | 31 | ola_money | 240 | 2024-08-03 09:00:00 |
| 2 | 31 | ola_money | 760 | 2024-08-04 18:30:00 |
| 3 | 32 | upi | 300 | 2024-08-05 10:00:00 |
| 4 | 33 | ola_money | 199 | 2024-08-31 23:59:00 |
| 5 | 33 | ola_money | 450 | 2024-09-01 00:05:00 |
| 6 | 34 | ola_money | 8 | 2024-08-10 12:00:00 |
| 7 | 34 | ola_money | 505 | 2024-08-11 12:00:00 |
CashbackCredit
| credit_id | payment_id | amount | credited_at |
|---|---|---|---|
| 1 | 1 | 24 | 2024-08-03 09:05:00 |
| 2 | 2 | 30 | 2024-08-04 18:35:00 |
| 3 | 2 | 10 | 2024-08-06 08:00:00 |
| 4 | 7 | 50 | 2024-08-11 12:10:00 |
Output
| payment_id | user_id | due | credited | shortfall |
|---|---|---|---|---|
| 2 | 31 | 50 | 40 | 10 |
| 4 | 33 | 19 | 0 | 19 |
How to solve Ola: Wallet Cashback That Was Not Fully Credited
Reconciliation compares two numbers per payment: what the rule says was due and what actually arrived. The due amount comes from the payment row alone — amount DIV 10 is 10% rounded down to whole rupees, and LEAST(…, 50) applies the cap. The credited amount is a sum over however many credit rows the payment has.
Joining credits with a LEFT JOIN keeps payments that received nothing; for them SUM(c.amount) is NULL, so COALESCE(…, 0) turns it into 0 both for the output and for the comparison in HAVING. The filter on method and month is on the payment and goes in WHERE, before grouping; the shortfall test needs the sum and goes in HAVING. A payment under ₹10 has 0 due and can never be short, and an over-credited payment is not short either.
Summing credits after a join is safe here because each payment row joins only its own credits. The alternative sums the credits in their own CTE first, computes due with FLOOR and a CASE cap, and joins the two — the pattern to prefer when more than one child table would be joined, since summing after joining two child tables multiplies rows.
Reference solution (MySQL)
SELECT p.payment_id, p.user_id,
LEAST(p.amount DIV 10, 50) AS due,
COALESCE(SUM(c.amount), 0) AS credited,
LEAST(p.amount DIV 10, 50) - COALESCE(SUM(c.amount), 0) AS shortfall
FROM RidePayment p
LEFT JOIN CashbackCredit c ON c.payment_id = p.payment_id
WHERE p.method = 'ola_money'
AND p.paid_at >= '2024-08-01' AND p.paid_at < '2024-09-01'
GROUP BY p.payment_id, p.user_id, p.amount
HAVING COALESCE(SUM(c.amount), 0) < LEAST(p.amount DIV 10, 50)
ORDER BY p.payment_idAnother way
WITH paid AS (SELECT payment_id, SUM(amount) AS credited FROM CashbackCredit GROUP BY payment_id),
due AS (
SELECT payment_id, user_id, CASE WHEN FLOOR(amount / 10) > 50 THEN 50 ELSE FLOOR(amount / 10) END AS due
FROM RidePayment
WHERE method = 'ola_money' AND DATE_FORMAT(paid_at, '%Y-%m') = '2024-08'
)
SELECT d.payment_id, d.user_id, d.due, IFNULL(p.credited, 0) AS credited, d.due - IFNULL(p.credited, 0) AS shortfall
FROM due d LEFT JOIN paid p ON p.payment_id = d.payment_id
WHERE IFNULL(p.credited, 0) < d.due
ORDER BY d.payment_id← Ola: New Riders Who Took a Second Ride Within 7 Days · Ola: Driver-Partners Cleared on All Four Documents →