Coupon Discount Spend by Campaign Prefix — SQL Problem
Every coupon code issued since 2024 has the form <CAMPAIGN>-<suffix>, e.g. DIWALI24-K7Q2: the campaign is the part before the first hyphen.
- Difficulty: Medium
- Topics: Strings, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #163
Problem statement
Every coupon code issued since 2024 has the form <CAMPAIGN>-<suffix>, e.g. DIWALI24-K7Q2: the campaign is the part before the first hyphen. Older codes carry no hyphen at all; they belong to the campaign LEGACY.
Return campaign, redemptions (number of redemptions) and total_discount (sum of discount) for every campaign with at least 2 redemptions, ordered by total_discount descending, then campaign ascending.
Tables
Table: CouponRedemption
| Column | Type |
|---|---|
| redemption_id | int |
| order_id | int |
| coupon_code | varchar |
| discount | int |
Primary key: redemption_id.
One row per coupon applied to an order, discount in rupees. Codes are upper-case letters, digits and hyphens.
Examples
Example 1
CouponRedemption
| redemption_id | order_id | coupon_code | discount |
|---|---|---|---|
| 1 | 9001 | DIWALI24-K7Q2 | 100 |
| 2 | 9002 | DIWALI24-ZZ81 | 75 |
| 3 | 9003 | IPLNIGHT-A1 | 60 |
| 4 | 9004 | WELCOME50 | 50 |
| 5 | 9005 | FREEDEL | 30 |
| 6 | 9006 | IPLNIGHT-B2-X | 120 |
| 7 | 9007 | MONSOON-77 | 40 |
| 8 | 9008 | WELCOME50 | 50 |
Output
| campaign | redemptions | total_discount |
|---|---|---|
| IPLNIGHT | 2 | 180 |
| DIWALI24 | 2 | 175 |
| LEGACY | 3 | 130 |
How to solve Coupon Discount Spend by Campaign Prefix
The grouping key is not a column but something derived from one. SUBSTRING_INDEX(coupon_code, '-', 1) returns the text before the first hyphen, so IPLNIGHT-B2-X is in IPLNIGHT — a split on the last hyphen would wrongly give IPLNIGHT-B2. For a code without a hyphen, SUBSTRING_INDEX returns the whole code, which would make WELCOME50 and FREEDEL separate campaigns; the CASE (testing LOCATE('-', code) = 0, or LIKE '%-%') sends them all to LEGACY.
Group by that expression — MySQL also accepts the alias in GROUP BY, but computing it in a derived table and grouping the outer query by its column is the portable way. HAVING keeps campaigns with two or more redemptions, and the order is by total discount with the campaign name breaking ties.
The cost is one pass and one group per campaign; the string functions are evaluated once per row.
Reference solution (MySQL)
SELECT CASE WHEN LOCATE('-', coupon_code) = 0 THEN 'LEGACY'
ELSE SUBSTRING_INDEX(coupon_code, '-', 1) END AS campaign,
COUNT(*) AS redemptions,
SUM(discount) AS total_discount
FROM CouponRedemption
GROUP BY CASE WHEN LOCATE('-', coupon_code) = 0 THEN 'LEGACY'
ELSE SUBSTRING_INDEX(coupon_code, '-', 1) END
HAVING COUNT(*) >= 2
ORDER BY total_discount DESC, campaignAnother way
SELECT campaign, COUNT(*) AS redemptions, SUM(discount) AS total_discount
FROM (SELECT IF(coupon_code LIKE '%-%', LEFT(coupon_code, INSTR(coupon_code, '-') - 1), 'LEGACY') AS campaign, discount
FROM CouponRedemption) t
GROUP BY campaign
HAVING COUNT(*) > 1
ORDER BY total_discount DESC, campaign← Packing Slip Item List for Each Quick Order · Restaurants Cooking Faster Than Their City Average →