Paytm: Cashback Paid Above the Per-User Campaign Cap

Medium
864.

Paytm: Cashback Paid Above the Per-User Campaign Cap

MediumJoinsAggregation

MySQL

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

ColumnType
campaign_codevarchar
titlevarchar
per_user_capint

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

ColumnTypeValues
cashback_idint
user_idint
campaign_codevarchar
amountint
statusenumcredited, reversed

Primary key: cashback_id.

One row per cashback credit; campaign_code always names a campaign.

Example

The rows must come back in the order shown.

Campaign

campaign_codetitleper_user_cap
HOLI50Holi bill-pay offer50
MOVIE100Movie Fridays100
FASTAG25FASTag recharge offer25

Cashback

cashback_iduser_idcampaign_codeamountstatus
11HOLI5030credited
21HOLI5030credited
31MOVIE100100credited
42HOLI5040credited
52HOLI5040reversed
62FASTAG2520credited
72FASTAG2510credited
83MOVIE10075credited
93MOVIE10075credited

Output

user_idcampaign_codecredited_totalper_user_capexcess
3MOVIE10015010050
1HOLI50605010
2FASTAG2530255
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: 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

ColumnType
campaign_codevarchar
titlevarchar
per_user_capint

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

ColumnType
cashback_idint
user_idint
campaign_codevarchar
amountint
statusenum(credited, reversed)

Primary key: cashback_id.

One row per cashback credit; campaign_code always names a campaign.

Examples

Example 1

Campaign

campaign_codetitleper_user_cap
HOLI50Holi bill-pay offer50
MOVIE100Movie Fridays100
FASTAG25FASTag recharge offer25

Cashback

cashback_iduser_idcampaign_codeamountstatus
11HOLI5030credited
21HOLI5030credited
31MOVIE100100credited
42HOLI5040credited
52HOLI5040reversed
62FASTAG2520credited
72FASTAG2510credited
83MOVIE10075credited
93MOVIE10075credited

Output

user_idcampaign_codecredited_totalper_user_capexcess
3MOVIE10015010050
1HOLI50605010
2FASTAG2530255

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_code

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