Promo Campaign Redemptions and Discount Burn

Medium
313.

Promo Campaign Redemptions and Discount Burn

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

ColumnType
redemption_idint
rider_idint
promo_codevarchar
redeemed_atdatetime
discountint

Primary key: redemption_id.

One row per ride on which a promo code was applied. Codes are stored in upper case.

Example

The rows must come back in the order shown.

PromoRedemption

redemption_idrider_idpromo_coderedeemed_atdiscount
181DIWALI-24-BLR2024-10-28 19:00:0075
282DIWALI-24-MUM2024-10-29 09:10:0060
381DIWALI-24-BLR2024-10-30 21:40:0075
483UPI502024-10-30 10:00:0050
584UPI502024-10-31 18:20:0050
685MONSOON-MUM2024-07-12 08:00:0040
786NEWUSER-1002024-10-01 12:00:00100
887NEWUSER-1002024-10-02 13:30:00100

Output

campaignredemptionsunique_riderstotal_discount
DIWALI32210
NEWUSER22200
UPI5022100
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.

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

ColumnType
redemption_idint
rider_idint
promo_codevarchar
redeemed_atdatetime
discountint

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_idrider_idpromo_coderedeemed_atdiscount
181DIWALI-24-BLR2024-10-28 19:00:0075
282DIWALI-24-MUM2024-10-29 09:10:0060
381DIWALI-24-BLR2024-10-30 21:40:0075
483UPI502024-10-30 10:00:0050
584UPI502024-10-31 18:20:0050
685MONSOON-MUM2024-07-12 08:00:0040
786NEWUSER-1002024-10-01 12:00:00100
887NEWUSER-1002024-10-02 13:30:00100

Output

campaignredemptionsunique_riderstotal_discount
DIWALI32210
NEWUSER22200
UPI5022100

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

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