Paytm: Spending Before and After Cashback for Each Campaign

Hard
871.

Paytm: Spending Before and After Cashback for Each Campaign

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

ColumnType
credit_idint
user_idint
campaign_codevarchar
credited_ondate
amountint

Primary key: credit_id.

One row per cashback credited; a user can be credited several times in one campaign.

Table: UserPayment

ColumnType
payment_idint
user_idint
paid_ondate
amountint

Primary key: payment_id.

One row per successful payment the user made on the app.

Example

The rows must come back in the order shown.

CashbackCredit

credit_iduser_idcampaign_codecredited_onamount
11HOLI502025-03-1450
21HOLI502025-03-2050
32HOLI502025-03-1530
43IPL1002025-04-01100

UserPayment

payment_iduser_idpaid_onamount
112025-02-27700
212025-02-28400
312025-03-14999
412025-03-20300
512025-03-28200
612025-03-29500
722025-03-01100
822025-03-1650
932025-03-31200
1032025-04-15600
1 more row

Output

campaign_codeuserscashback_paidspend_beforespend_afteruplift
IPL1001100200600400
HOLI50213050055050
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.

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

ColumnType
credit_idint
user_idint
campaign_codevarchar
credited_ondate
amountint

Primary key: credit_id.

One row per cashback credited; a user can be credited several times in one campaign.

Table: UserPayment

ColumnType
payment_idint
user_idint
paid_ondate
amountint

Primary key: payment_id.

One row per successful payment the user made on the app.

Examples

Example 1

CashbackCredit

credit_iduser_idcampaign_codecredited_onamount
11HOLI502025-03-1450
21HOLI502025-03-2050
32HOLI502025-03-1530
43IPL1002025-04-01100

UserPayment

payment_iduser_idpaid_onamount
112025-02-27700
212025-02-28400
312025-03-14999
412025-03-20300
512025-03-28200
612025-03-29500
722025-03-01100
822025-03-1650
932025-03-31200
1032025-04-15600
1142025-03-20800

Output

campaign_codeuserscashback_paidspend_beforespend_afteruplift
IPL1001100200600400
HOLI50213050055050

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_code

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