Coupon Discount Spend by Campaign Prefix

Medium
163.

Coupon Discount Spend by Campaign Prefix

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

ColumnType
redemption_idint
order_idint
coupon_codevarchar
discountint

Primary key: redemption_id.

One row per coupon applied to an order, discount in rupees. Codes are upper-case letters, digits and hyphens.

Example

The rows must come back in the order shown.

CouponRedemption

redemption_idorder_idcoupon_codediscount
19001DIWALI24-K7Q2100
29002DIWALI24-ZZ8175
39003IPLNIGHT-A160
49004WELCOME5050
59005FREEDEL30
69006IPLNIGHT-B2-X120
79007MONSOON-7740
89008WELCOME5050

Output

campaignredemptionstotal_discount
IPLNIGHT2180
DIWALI242175
LEGACY3130
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.

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

ColumnType
redemption_idint
order_idint
coupon_codevarchar
discountint

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_idorder_idcoupon_codediscount
19001DIWALI24-K7Q2100
29002DIWALI24-ZZ8175
39003IPLNIGHT-A160
49004WELCOME5050
59005FREEDEL30
69006IPLNIGHT-B2-X120
79007MONSOON-7740
89008WELCOME5050

Output

campaignredemptionstotal_discount
IPLNIGHT2180
DIWALI242175
LEGACY3130

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

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