Promo Campaign Redemptions and Discount Burn — SQL Problem
Marketing issues promo codes named <CAMPAIGN>-<variant>, such as DIWALI-24-BLR or MONSOON-MUM; the campaign is the part before the first hyphen.
- Difficulty: Medium
- Topics: Strings, Aggregation
- Dialect: MySQL
- Problem: #313
Problem statement
Marketing issues promo codes named <CAMPAIGN>-<variant>, such as DIWALI-24-BLR or MONSOON-MUM; the campaign is the part before the first hyphen. A few legacy codes have no hyphen at all (UPI50) — the whole code is then its own campaign.
For every campaign with at least 2 redemptions, return campaign, redemptions, unique_riders (distinct riders who redeemed it) and total_discount (rupees). Order by total_discount descending, then campaign ascending.
Tables
Table: PromoRedemption
| Column | Type |
|---|---|
| redemption_id | int |
| rider_id | int |
| promo_code | varchar |
| redeemed_at | datetime |
| discount | int |
Primary key: redemption_id.
One row per ride on which a promo code was applied. Codes are stored in upper case.
Examples
Example 1
PromoRedemption
| redemption_id | rider_id | promo_code | redeemed_at | discount |
|---|---|---|---|---|
| 1 | 81 | DIWALI-24-BLR | 2024-10-28 19:00:00 | 75 |
| 2 | 82 | DIWALI-24-MUM | 2024-10-29 09:10:00 | 60 |
| 3 | 81 | DIWALI-24-BLR | 2024-10-30 21:40:00 | 75 |
| 4 | 83 | UPI50 | 2024-10-30 10:00:00 | 50 |
| 5 | 84 | UPI50 | 2024-10-31 18:20:00 | 50 |
| 6 | 85 | MONSOON-MUM | 2024-07-12 08:00:00 | 40 |
| 7 | 86 | NEWUSER-100 | 2024-10-01 12:00:00 | 100 |
| 8 | 87 | NEWUSER-100 | 2024-10-02 13:30:00 | 100 |
Output
| campaign | redemptions | unique_riders | total_discount |
|---|---|---|---|
| DIWALI | 3 | 2 | 210 |
| NEWUSER | 2 | 2 | 200 |
| UPI50 | 2 | 2 | 100 |
How to solve Promo Campaign Redemptions and Discount Burn
The campaign is computed from the code: everything before the first hyphen. SUBSTRING_INDEX(promo_code, '-', 1) does exactly that, and conveniently returns the whole string when no hyphen exists, so the legacy UPI50 becomes its own campaign without a special case. DIWALI-24-BLR and DIWALI-24-MUM both map to DIWALI, which is the point of the report.
Group by that expression and compute three aggregates at once: COUNT(*) redemptions, COUNT(DISTINCT rider_id) unique riders (a rider using the code twice counts once), and SUM(discount). The two-redemption minimum filters groups, so it goes in HAVING.
The alternative finds the hyphen with LOCATE and cuts with LEFT, with a CASE for codes where LOCATE returns 0 — a reminder that LEFT(code, 0 - 1) would return an empty string. Either way it is one scan and a group-by; storing the campaign as its own column would let an index do the grouping.
Reference solution (MySQL)
SELECT SUBSTRING_INDEX(promo_code, '-', 1) AS campaign,
COUNT(*) AS redemptions,
COUNT(DISTINCT rider_id) AS unique_riders,
SUM(discount) AS total_discount
FROM PromoRedemption
GROUP BY SUBSTRING_INDEX(promo_code, '-', 1)
HAVING COUNT(*) >= 2
ORDER BY total_discount DESC, campaignAnother way
SELECT campaign, COUNT(*) AS redemptions, COUNT(DISTINCT rider_id) AS unique_riders, SUM(discount) AS total_discount
FROM (
SELECT rider_id, discount,
CASE WHEN LOCATE('-', promo_code) = 0 THEN promo_code
ELSE LEFT(promo_code, LOCATE('-', promo_code) - 1) END AS campaign
FROM PromoRedemption
) r
GROUP BY campaign
HAVING COUNT(*) > 1
ORDER BY total_discount DESC, campaign← Fleet Utilisation by Vehicle Type for One Week · Driver Idle Gaps Longer Than 90 Minutes →