Dream11: Promo Code Redemptions per Campaign

Easy
627.

Dream11: Promo Code Redemptions per Campaign

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

ColumnType
redemption_idint
user_idint
promo_codevarchar
bonus_amountint
redeemed_ondate

Primary key: redemption_id.

One row per code applied to a wallet. Codes are upper case and always contain at least one hyphen.

Example

The rows must come back in the order shown.

PromoRedemption

redemption_iduser_idpromo_codebonus_amountredeemed_on
121WELCOME-A7K2502025-04-01
222WELCOME-B9Q1502025-04-01
321IPLFINAL-X4M81002025-05-25
423IPLFINAL-2-PL9Z1002025-05-25
523IPLFINAL-Z1Q51002025-05-26
624KABADDI50-K8J3502025-06-02
724KABADDI50-M2D7502025-06-03

Output

campaignredemptionsunique_userstotal_bonus
IPLFINAL32300
KABADDI5021100
WELCOME22100
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.

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

ColumnType
redemption_idint
user_idint
promo_codevarchar
bonus_amountint
redeemed_ondate

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_iduser_idpromo_codebonus_amountredeemed_on
121WELCOME-A7K2502025-04-01
222WELCOME-B9Q1502025-04-01
321IPLFINAL-X4M81002025-05-25
423IPLFINAL-2-PL9Z1002025-05-25
523IPLFINAL-Z1Q51002025-05-26
624KABADDI50-K8J3502025-06-02
724KABADDI50-M2D7502025-06-03

Output

campaignredemptionsunique_userstotal_bonus
IPLFINAL32300
KABADDI5021100
WELCOME22100

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, campaign

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