Paytm: Cashback Paid Above the Per-User Campaign Cap
Every cashback campaign has a per-user cap, but a bug or a race between two payments can credit someone more. The rewards finance team audits the leakage.
- Difficulty: Medium
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #864
Problem statement
Every cashback campaign has a per-user cap, but a bug or a race between two payments can credit someone more. The rewards finance team audits the leakage.
For each (user, campaign), add up the credited cashback (reversed rows were clawed back and do not count). Return every pair whose credited total is strictly greater than the campaign's per_user_cap, with the columns user_id, campaign_code, credited_total, per_user_cap and excess (total minus cap). Return the rows ordered by excess descending, then user_id, then campaign_code.
A practice scenario set at Paytm: the tables and data are invented, and this is not a question from, or endorsed by, Paytm.
Tables
Table: Campaign
| Column | Type |
|---|---|
| campaign_code | varchar |
| title | varchar |
| per_user_cap | int |
Primary key: campaign_code.
One row per cashback offer; per_user_cap is the most one user may earn from it, in rupees.
Table: Cashback
| Column | Type |
|---|---|
| cashback_id | int |
| user_id | int |
| campaign_code | varchar |
| amount | int |
| status | enum(credited, reversed) |
Primary key: cashback_id.
One row per cashback credit; campaign_code always names a campaign.
Examples
Example 1
Campaign
| campaign_code | title | per_user_cap |
|---|---|---|
| HOLI50 | Holi bill-pay offer | 50 |
| MOVIE100 | Movie Fridays | 100 |
| FASTAG25 | FASTag recharge offer | 25 |
Cashback
| cashback_id | user_id | campaign_code | amount | status |
|---|---|---|---|---|
| 1 | 1 | HOLI50 | 30 | credited |
| 2 | 1 | HOLI50 | 30 | credited |
| 3 | 1 | MOVIE100 | 100 | credited |
| 4 | 2 | HOLI50 | 40 | credited |
| 5 | 2 | HOLI50 | 40 | reversed |
| 6 | 2 | FASTAG25 | 20 | credited |
| 7 | 2 | FASTAG25 | 10 | credited |
| 8 | 3 | MOVIE100 | 75 | credited |
| 9 | 3 | MOVIE100 | 75 | credited |
Output
| user_id | campaign_code | credited_total | per_user_cap | excess |
|---|---|---|---|---|
| 3 | MOVIE100 | 150 | 100 | 50 |
| 1 | HOLI50 | 60 | 50 | 10 |
| 2 | FASTAG25 | 30 | 25 | 5 |
How to solve Paytm: Cashback Paid Above the Per-User Campaign Cap
The cap applies to what one user earned from one campaign, so the unit of the answer is the (user, campaign) pair. Filter to credited rows, group by the pair, and SUM(amount) gives what was actually paid; a reversal took its money back, so it must not be added (user 2's Holi credits net to 40 once the reversed row is excluded).
Joining Campaign brings in per_user_cap. Because it is constant within a group it can be listed in GROUP BY and selected. The comparison is on an aggregate, so it goes in HAVING SUM(c.amount) > p.per_user_cap — strictly greater, which leaves user 1's 100 on a 100 cap out. excess is the same difference, and the order is excess descending with the user and campaign as tie-breakers.
The alternative aggregates in a CTE first and joins the much smaller totals to the campaigns, filtering with WHERE. Both scan Cashback once.
Reference solution (MySQL)
SELECT c.user_id, c.campaign_code, SUM(c.amount) AS credited_total, p.per_user_cap,
SUM(c.amount) - p.per_user_cap AS excess
FROM Cashback c
JOIN Campaign p ON p.campaign_code = c.campaign_code
WHERE c.status = 'credited'
GROUP BY c.user_id, c.campaign_code, p.per_user_cap
HAVING SUM(c.amount) > p.per_user_cap
ORDER BY excess DESC, c.user_id, c.campaign_codeAnother way
WITH totals AS (SELECT user_id, campaign_code, SUM(amount) AS credited_total FROM Cashback WHERE status = 'credited' GROUP BY user_id, campaign_code)
SELECT t.user_id, t.campaign_code, t.credited_total, p.per_user_cap, t.credited_total - p.per_user_cap AS excess
FROM totals t JOIN Campaign p ON p.campaign_code = t.campaign_code
WHERE t.credited_total > p.per_user_cap
ORDER BY excess DESC, t.user_id, t.campaign_code← Paytm: Mobile Numbers That Switched Operator Between Recharges · Paytm: Merchant Days When QR Collections Fell by More Than Half →