Ola: Wallet Cashback That Was Not Fully Credited

Medium
836.

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

ColumnTypeValues
payment_idint
user_idint
methodenumola_money, upi, cash, card
amountint
paid_atdatetime

Primary key: payment_id.

One row per ride payment, in whole rupees.

Table: CashbackCredit

ColumnType
credit_idint
payment_idint
amountint
credited_atdatetime

Primary key: credit_id.

One row per cashback credit; a payment can have zero, one or several.

Example

The rows must come back in the order shown.

RidePayment

payment_iduser_idmethodamountpaid_at
131ola_money2402024-08-03 09:00:00
231ola_money7602024-08-04 18:30:00
332upi3002024-08-05 10:00:00
433ola_money1992024-08-31 23:59:00
533ola_money4502024-09-01 00:05:00
634ola_money82024-08-10 12:00:00
734ola_money5052024-08-11 12:00:00

CashbackCredit

credit_idpayment_idamountcredited_at
11242024-08-03 09:05:00
22302024-08-04 18:35:00
32102024-08-06 08:00:00
47502024-08-11 12:10:00

Output

payment_iduser_idduecreditedshortfall
231504010
43319019
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: 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

ColumnType
payment_idint
user_idint
methodenum(ola_money, upi, cash, card)
amountint
paid_atdatetime

Primary key: payment_id.

One row per ride payment, in whole rupees.

Table: CashbackCredit

ColumnType
credit_idint
payment_idint
amountint
credited_atdatetime

Primary key: credit_id.

One row per cashback credit; a payment can have zero, one or several.

Examples

Example 1

RidePayment

payment_iduser_idmethodamountpaid_at
131ola_money2402024-08-03 09:00:00
231ola_money7602024-08-04 18:30:00
332upi3002024-08-05 10:00:00
433ola_money1992024-08-31 23:59:00
533ola_money4502024-09-01 00:05:00
634ola_money82024-08-10 12:00:00
734ola_money5052024-08-11 12:00:00

CashbackCredit

credit_idpayment_idamountcredited_at
11242024-08-03 09:05:00
22302024-08-04 18:35:00
32102024-08-06 08:00:00
47502024-08-11 12:10:00

Output

payment_iduser_idduecreditedshortfall
231504010
43319019

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_id

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