PhonePe: Rewards Campaign Redemption Report on 1 April
Each rewards campaign issues coupons that a user can redeem until expires_on. On the report date 2025-04-01 the rewards team reviews every campaign.
- Difficulty: Medium
- Topics: Conditional Logic, Joins, Dates
- Dialect: MySQL
- Problem: #890
Problem statement
Each rewards campaign issues coupons that a user can redeem until expires_on. On the report date 2025-04-01 the rewards team reviews every campaign.
Return campaign_id, campaign_name, issued (coupons issued), redeemed (with a redeemed_on date), lapsed (not redeemed and expires_on before 2025-04-01), still_open (not redeemed and expires_on on or after 2025-04-01) and redeemed_pct = 100 × redeemed ÷ issued rounded to 2 decimals — NULL for a campaign that issued nothing. List every campaign, with 0 counts where nothing applies, ordered by campaign_id. A redemption always happens on or before expiry and before the report date.
A practice scenario set at PhonePe: the tables and data are invented, and this is not a question from, or endorsed by, PhonePe.
Tables
Table: RewardCampaign
| Column | Type |
|---|---|
| campaign_id | int |
| campaign_name | varchar |
Primary key: campaign_id.
One row per rewards campaign.
Table: RewardCoupon
| Column | Type |
|---|---|
| coupon_id | int |
| campaign_id | int |
| user_id | int |
| issued_on | date |
| expires_on | date |
| redeemed_on | date |
Primary key: coupon_id.
One row per coupon issued; redeemed_on is NULL until the coupon is used.
Examples
Example 1
RewardCampaign
| campaign_id | campaign_name |
|---|---|
| 1 | Holi Cashback |
| 2 | First Bill Payment |
| 3 | Travel Week |
RewardCoupon
| coupon_id | campaign_id | user_id | issued_on | expires_on | redeemed_on |
|---|---|---|---|---|---|
| 101 | 1 | 81 | 2025-03-10 | 2025-03-25 | 2025-03-12 |
| 102 | 1 | 82 | 2025-03-10 | 2025-03-25 | NULL |
| 103 | 1 | 83 | 2025-03-15 | 2025-04-01 | NULL |
| 104 | 2 | 81 | 2025-03-20 | 2025-04-20 | 2025-03-31 |
| 105 | 2 | 84 | 2025-03-21 | 2025-04-21 | NULL |
| 106 | 2 | 85 | 2025-02-01 | 2025-03-31 | NULL |
Output
| campaign_id | campaign_name | issued | redeemed | lapsed | still_open | redeemed_pct |
|---|---|---|---|---|---|---|
| 1 | Holi Cashback | 3 | 1 | 1 | 1 | 33.33 |
| 2 | First Bill Payment | 3 | 1 | 1 | 1 | 33.33 |
| 3 | Travel Week | 0 | 0 | 0 | 0 | NULL |
How to solve PhonePe: Rewards Campaign Redemption Report on 1 April
One row per campaign with several counts is conditional aggregation over a LEFT JOIN, so campaigns with no coupons still get a row. Every coupon is in exactly one bucket: redeemed (it has a date), lapsed (no date and the expiry has passed) or still open (no date and the expiry is on or after the report date — a coupon expiring on 1 April can still be used that day).
The outer join produces one all-NULL row for an empty campaign, and that row must not land in any bucket. COUNT(r.coupon_id) ignores it, and the lapsed/open conditions start with r.coupon_id IS NOT NULL so the NULL redeemed_on of the padding is not mistaken for an unredeemed coupon. The rate divides by NULLIF(COUNT(r.coupon_id), 0), which is NULL for an empty campaign, so the whole expression is NULL instead of an error or a misleading 0.
The alternative computes each count with its own correlated COUNT(*) and avoids the padding question entirely; it reads the coupons four times per campaign, where the main query reads them once.
Reference solution (MySQL)
SELECT c.campaign_id, c.campaign_name,
COUNT(r.coupon_id) AS issued,
SUM(CASE WHEN r.redeemed_on IS NOT NULL THEN 1 ELSE 0 END) AS redeemed,
SUM(CASE WHEN r.coupon_id IS NOT NULL AND r.redeemed_on IS NULL AND r.expires_on < '2025-04-01' THEN 1 ELSE 0 END) AS lapsed,
SUM(CASE WHEN r.coupon_id IS NOT NULL AND r.redeemed_on IS NULL AND r.expires_on >= '2025-04-01' THEN 1 ELSE 0 END) AS still_open,
ROUND(100 * SUM(CASE WHEN r.redeemed_on IS NOT NULL THEN 1 ELSE 0 END) / NULLIF(COUNT(r.coupon_id), 0), 2) AS redeemed_pct
FROM RewardCampaign c
LEFT JOIN RewardCoupon r ON r.campaign_id = c.campaign_id
GROUP BY c.campaign_id, c.campaign_name
ORDER BY c.campaign_idAnother way
SELECT c.campaign_id, c.campaign_name, t.issued, t.redeemed, t.lapsed, t.still_open,
ROUND(100 * t.redeemed / NULLIF(t.issued, 0), 2) AS redeemed_pct
FROM RewardCampaign c
JOIN (
SELECT c2.campaign_id,
(SELECT COUNT(*) FROM RewardCoupon r WHERE r.campaign_id = c2.campaign_id) AS issued,
(SELECT COUNT(redeemed_on) FROM RewardCoupon r WHERE r.campaign_id = c2.campaign_id) AS redeemed,
(SELECT COUNT(*) FROM RewardCoupon r WHERE r.campaign_id = c2.campaign_id AND r.redeemed_on IS NULL AND r.expires_on < '2025-04-01') AS lapsed,
(SELECT COUNT(*) FROM RewardCoupon r WHERE r.campaign_id = c2.campaign_id AND r.redeemed_on IS NULL AND r.expires_on >= '2025-04-01') AS still_open
FROM RewardCampaign c2
) t ON t.campaign_id = c.campaign_id
ORDER BY c.campaign_id← PhonePe: Users Who Only Send, Only Receive or Do Both in October · PhonePe: Users Paying Three New Payees Within One Hour →