PhonePe: Rewards Campaign Redemption Report on 1 April

Medium
890.

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.

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

ColumnType
campaign_idint
campaign_namevarchar

Primary key: campaign_id.

One row per rewards campaign.

Table: RewardCoupon

ColumnType
coupon_idint
campaign_idint
user_idint
issued_ondate
expires_ondate
redeemed_ondate

Primary key: coupon_id.

One row per coupon issued; redeemed_on is NULL until the coupon is used.

Example

The rows must come back in the order shown.

RewardCampaign

campaign_idcampaign_name
1Holi Cashback
2First Bill Payment
3Travel Week

RewardCoupon

coupon_idcampaign_iduser_idissued_onexpires_onredeemed_on
1011812025-03-102025-03-252025-03-12
1021822025-03-102025-03-25NULL
1031832025-03-152025-04-01NULL
1042812025-03-202025-04-202025-03-31
1052842025-03-212025-04-21NULL
1062852025-02-012025-03-31NULL

Output

campaign_idcampaign_nameissuedredeemedlapsedstill_openredeemed_pct
1Holi Cashback311133.33
2First Bill Payment311133.33
3Travel Week0000NULL
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.

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

ColumnType
campaign_idint
campaign_namevarchar

Primary key: campaign_id.

One row per rewards campaign.

Table: RewardCoupon

ColumnType
coupon_idint
campaign_idint
user_idint
issued_ondate
expires_ondate
redeemed_ondate

Primary key: coupon_id.

One row per coupon issued; redeemed_on is NULL until the coupon is used.

Examples

Example 1

RewardCampaign

campaign_idcampaign_name
1Holi Cashback
2First Bill Payment
3Travel Week

RewardCoupon

coupon_idcampaign_iduser_idissued_onexpires_onredeemed_on
1011812025-03-102025-03-252025-03-12
1021822025-03-102025-03-25NULL
1031832025-03-152025-04-01NULL
1042812025-03-202025-04-202025-03-31
1052842025-03-212025-04-21NULL
1062852025-02-012025-03-31NULL

Output

campaign_idcampaign_nameissuedredeemedlapsedstill_openredeemed_pct
1Holi Cashback311133.33
2First Bill Payment311133.33
3Travel Week0000NULL

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_id

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