Paytm: Spending Before and After Cashback for Each Campaign
The rewards team judges a cashback campaign by whether users spend more after receiving it.
- Difficulty: Hard
- Topics: Subqueries, Joins, Dates, Aggregation
- Dialect: MySQL
- Problem: #871
Problem statement
The rewards team judges a cashback campaign by whether users spend more after receiving it. For each user in a campaign, the reference day is their first credit in that campaign.
Per (campaign, user), take their payments in the 14 days before the reference day (reference − 14 … reference − 1) and the 14 days after it (reference + 1 … reference + 14); payments on the reference day itself are ignored. For every campaign return campaign_code, users (distinct users credited), cashback_paid (all its credits), spend_before, spend_after (the windows summed over its users; 0 if none) and uplift = spend_after − spend_before. Return the rows ordered by uplift descending, 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: CashbackCredit
| Column | Type |
|---|---|
| credit_id | int |
| user_id | int |
| campaign_code | varchar |
| credited_on | date |
| amount | int |
Primary key: credit_id.
One row per cashback credited; a user can be credited several times in one campaign.
Table: UserPayment
| Column | Type |
|---|---|
| payment_id | int |
| user_id | int |
| paid_on | date |
| amount | int |
Primary key: payment_id.
One row per successful payment the user made on the app.
Examples
Example 1
CashbackCredit
| credit_id | user_id | campaign_code | credited_on | amount |
|---|---|---|---|---|
| 1 | 1 | HOLI50 | 2025-03-14 | 50 |
| 2 | 1 | HOLI50 | 2025-03-20 | 50 |
| 3 | 2 | HOLI50 | 2025-03-15 | 30 |
| 4 | 3 | IPL100 | 2025-04-01 | 100 |
UserPayment
| payment_id | user_id | paid_on | amount |
|---|---|---|---|
| 1 | 1 | 2025-02-27 | 700 |
| 2 | 1 | 2025-02-28 | 400 |
| 3 | 1 | 2025-03-14 | 999 |
| 4 | 1 | 2025-03-20 | 300 |
| 5 | 1 | 2025-03-28 | 200 |
| 6 | 1 | 2025-03-29 | 500 |
| 7 | 2 | 2025-03-01 | 100 |
| 8 | 2 | 2025-03-16 | 50 |
| 9 | 3 | 2025-03-31 | 200 |
| 10 | 3 | 2025-04-15 | 600 |
| 11 | 4 | 2025-03-20 | 800 |
Output
| campaign_code | users | cashback_paid | spend_before | spend_after | uplift |
|---|---|---|---|---|---|
| IPL100 | 1 | 100 | 200 | 600 | 400 |
| HOLI50 | 2 | 130 | 500 | 550 | 50 |
How to solve Paytm: Spending Before and After Cashback for Each Campaign
Break the report into levels. Per (campaign, user): the reference day is MIN(credited_on), so user 1's second Holi credit on 20 March does not move their windows. Joining that row to the user's payments and summing with two conditional expressions gives the spend in each window: BETWEEN ref − 14 AND ref − 1 before, BETWEEN ref + 1 AND ref + 14 after. Both ends are inclusive, so a payment exactly 14 days away counts, one 15 days away does not, and the reference day's own payment (user 1's 999) falls in neither. The LEFT JOIN keeps users with no payments, and COALESCE(…, 0) turns their empty sums into 0.
Per campaign: add the users' windows and count the users. cashback_paid must include every credit, but joining credits to payments would repeat each credit once per payment, so it is computed separately (the paid CTE) and joined on the campaign. uplift is the after total minus the before total.
The alternative expresses the windows with DATEDIFF(...) BETWEEN 1 AND 14 in correlated subqueries and fetches the cashback total with a scalar subquery. Payments of a user who is in two campaigns are counted once per campaign, which is the intended reading — each campaign is judged on its own.
Reference solution (MySQL)
WITH firsts AS (
SELECT campaign_code, user_id, MIN(credited_on) AS ref_day
FROM CashbackCredit
GROUP BY campaign_code, user_id
), windows AS (
SELECT f.campaign_code, f.user_id,
COALESCE(SUM(CASE WHEN p.paid_on BETWEEN DATE_SUB(f.ref_day, INTERVAL 14 DAY) AND DATE_SUB(f.ref_day, INTERVAL 1 DAY) THEN p.amount END), 0) AS before_amt,
COALESCE(SUM(CASE WHEN p.paid_on BETWEEN DATE_ADD(f.ref_day, INTERVAL 1 DAY) AND DATE_ADD(f.ref_day, INTERVAL 14 DAY) THEN p.amount END), 0) AS after_amt
FROM firsts f
LEFT JOIN UserPayment p ON p.user_id = f.user_id
GROUP BY f.campaign_code, f.user_id
), paid AS (
SELECT campaign_code, SUM(amount) AS cashback_paid FROM CashbackCredit GROUP BY campaign_code
)
SELECT w.campaign_code, COUNT(*) AS users, p.cashback_paid,
SUM(w.before_amt) AS spend_before, SUM(w.after_amt) AS spend_after,
SUM(w.after_amt) - SUM(w.before_amt) AS uplift
FROM windows w
JOIN paid p ON p.campaign_code = w.campaign_code
GROUP BY w.campaign_code, p.cashback_paid
ORDER BY uplift DESC, w.campaign_codeAnother way
WITH firsts AS (SELECT campaign_code, user_id, MIN(credited_on) AS ref_day FROM CashbackCredit GROUP BY campaign_code, user_id),
per_user AS (
SELECT f.campaign_code,
IFNULL((SELECT SUM(p.amount) FROM UserPayment p WHERE p.user_id = f.user_id AND DATEDIFF(f.ref_day, p.paid_on) BETWEEN 1 AND 14), 0) AS b,
IFNULL((SELECT SUM(p.amount) FROM UserPayment p WHERE p.user_id = f.user_id AND DATEDIFF(p.paid_on, f.ref_day) BETWEEN 1 AND 14), 0) AS a
FROM firsts f
)
SELECT u.campaign_code, COUNT(*) AS users,
(SELECT SUM(c.amount) FROM CashbackCredit c WHERE c.campaign_code = u.campaign_code) AS cashback_paid,
SUM(u.b) AS spend_before, SUM(u.a) AS spend_after, SUM(u.a - u.b) AS uplift
FROM per_user u
GROUP BY u.campaign_code
ORDER BY uplift DESC, u.campaign_code← Paytm: Merchant Loan Outstanding as of 31 January 2025 · Paytm: Return Flights Booked Within Ten Days →