Dream11: Promo Code Redemptions per Campaign — SQL Problem
Dream11's marketing team issues bonus-cash promo codes in campaigns.
- Difficulty: Easy
- Topics: Strings, Aggregation
- Dialect: MySQL
- Problem: #627
Problem statement
Dream11's marketing team issues bonus-cash promo codes in campaigns. Every code starts with the campaign name and a hyphen, followed by a unique suffix (IPLFINAL-X4M8); a suffix may itself contain hyphens (IPLFINAL-2-PL9Z), so the campaign is the part before the first hyphen.
For each campaign, return campaign, redemptions (rows), unique_users (distinct users who redeemed) and total_bonus (sum of bonus_amount). Order by total_bonus descending, then by campaign ascending.
A practice scenario set at Dream11: the tables and data are invented, and this is not a question from, or endorsed by, Dream11.
Tables
Table: PromoRedemption
| Column | Type |
|---|---|
| redemption_id | int |
| user_id | int |
| promo_code | varchar |
| bonus_amount | int |
| redeemed_on | date |
Primary key: redemption_id.
One row per code applied to a wallet. Codes are upper case and always contain at least one hyphen.
Examples
Example 1
PromoRedemption
| redemption_id | user_id | promo_code | bonus_amount | redeemed_on |
|---|---|---|---|---|
| 1 | 21 | WELCOME-A7K2 | 50 | 2025-04-01 |
| 2 | 22 | WELCOME-B9Q1 | 50 | 2025-04-01 |
| 3 | 21 | IPLFINAL-X4M8 | 100 | 2025-05-25 |
| 4 | 23 | IPLFINAL-2-PL9Z | 100 | 2025-05-25 |
| 5 | 23 | IPLFINAL-Z1Q5 | 100 | 2025-05-26 |
| 6 | 24 | KABADDI50-K8J3 | 50 | 2025-06-02 |
| 7 | 24 | KABADDI50-M2D7 | 50 | 2025-06-03 |
Output
| campaign | redemptions | unique_users | total_bonus |
|---|---|---|---|
| IPLFINAL | 3 | 2 | 300 |
| KABADDI50 | 2 | 1 | 100 |
| WELCOME | 2 | 2 | 100 |
How to solve Dream11: Promo Code Redemptions per Campaign
The grouping key is hidden inside a string, so the first job is to extract it. SUBSTRING_INDEX(promo_code, '-', 1) returns the text before the first occurrence of the delimiter — exactly the campaign, even when the suffix contains more hyphens. Taking the part before the last hyphen, or a fixed number of characters, would break on IPLFINAL-2-PL9Z or on campaigns of different lengths.
Once each row has its campaign, the rest is ordinary aggregation: COUNT(*) counts redemptions, COUNT(DISTINCT user_id) counts people (a user who redeemed two codes of a campaign counts once), and SUM(bonus_amount) is the cost of the campaign. Grouping by the alias works in MySQL; the alternative computes the campaign in a derived table with LEFT(promo_code, LOCATE('-', promo_code) - 1) and groups outside, which is the portable way to write it.
The order puts the most expensive campaigns first and breaks ties by name. One scan of the table.
Reference solution (MySQL)
SELECT SUBSTRING_INDEX(promo_code, '-', 1) AS campaign,
COUNT(*) AS redemptions,
COUNT(DISTINCT user_id) AS unique_users,
SUM(bonus_amount) AS total_bonus
FROM PromoRedemption
GROUP BY campaign
ORDER BY total_bonus DESC, campaignAnother way
SELECT campaign, COUNT(*) AS redemptions, COUNT(DISTINCT user_id) AS unique_users, SUM(bonus_amount) AS total_bonus
FROM (SELECT LEFT(promo_code, LOCATE('-', promo_code) - 1) AS campaign, user_id, bonus_amount FROM PromoRedemption) x
GROUP BY campaign
ORDER BY total_bonus DESC, campaign← Dream11: Saved Teams Over the 100-Credit Cap · Dream11: Withdrawals That Missed the 24-Hour Payout Promise →