Coupon Redemption Summary per Code

Easy
102.

Coupon Redemption Summary per Code

The growth team reviews every coupon it has created, including the ones nobody used. Each use of a coupon on an order is one row of coupon_redemptions.

Return one row per coupon in coupons with the columns code, redemptions (the number of times it was used) and total_discount (the rupees it gave away). A coupon never used shows 0 in both columns. Return the rows in any order.

Tables

Table: coupons

ColumnTypeValues
codevarchar
discount_typeenumflat, percent
valid_tilldate

Primary key: code.

One row per coupon the marketing team created.

Table: coupon_redemptions

ColumnType
redemption_idint
codevarchar
order_idint
discount_amountint

Primary key: redemption_id.

One row per order a coupon was applied to; discount_amount in rupees. code is always in coupons.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

coupons

codediscount_typevalid_till
WELCOME100flat2024-12-31
FESTIVE20percent2024-11-15
UPI50flat2024-12-31
DIWALI500flat2024-11-05

coupon_redemptions

redemption_idcodeorder_iddiscount_amount
1WELCOME1008101100
2FESTIVE208102340
3WELCOME1008103100
4FESTIVE2081041200
5UPI50810550
6FESTIVE20810685

Output

coderedemptionstotal_discount
DIWALI50000
FESTIVE2031625
UPI50150
WELCOME1002200
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 Redemption Summary per Code — SQL Joins Problem

The growth team reviews every coupon it has created, including the ones nobody used. Each use of a coupon on an order is one row of coupon_redemptions.

  • Difficulty: Easy
  • Topics: Joins, Aggregation
  • Dialect: MySQL
  • Problem: #102

Problem statement

The growth team reviews every coupon it has created, including the ones nobody used. Each use of a coupon on an order is one row of coupon_redemptions.

Return one row per coupon in coupons with the columns code, redemptions (the number of times it was used) and total_discount (the rupees it gave away). A coupon never used shows 0 in both columns. Return the rows in any order.

Tables

Table: coupons

ColumnType
codevarchar
discount_typeenum(flat, percent)
valid_tilldate

Primary key: code.

One row per coupon the marketing team created.

Table: coupon_redemptions

ColumnType
redemption_idint
codevarchar
order_idint
discount_amountint

Primary key: redemption_id.

One row per order a coupon was applied to; discount_amount in rupees. code is always in coupons.

Examples

Example 1

coupons

codediscount_typevalid_till
WELCOME100flat2024-12-31
FESTIVE20percent2024-11-15
UPI50flat2024-12-31
DIWALI500flat2024-11-05

coupon_redemptions

redemption_idcodeorder_iddiscount_amount
1WELCOME1008101100
2FESTIVE208102340
3WELCOME1008103100
4FESTIVE2081041200
5UPI50810550
6FESTIVE20810685

Output

coderedemptionstotal_discount
DIWALI50000
FESTIVE2031625
UPI50150
WELCOME1002200

How to solve Coupon Redemption Summary per Code

Every coupon must appear, used or not, so start from coupons and LEFT JOIN the redemptions. A used coupon gets one joined row per redemption; an unused one gets a single row with NULL in every redemption column.

That NULL row is why the aggregates need care. COUNT(r.redemption_id) counts non-NULL values, so the unused coupon counts 0 — COUNT(*) would count the padding row and report 1. SUM(r.discount_amount) over nothing but a NULL is NULL, and COALESCE(..., 0) turns it into the 0 the statement asks for.

Correlated scalar subqueries give the same result one coupon at a time, and there COUNT(*) is right because a coupon with no redemptions has no rows at all. Pre-aggregating the redemptions per code and LEFT JOINing the totals is the third shape, and on a large redemptions table usually the fastest: one grouped pass, then one row per coupon to join.

Reference solution (MySQL)

SELECT c.code, COUNT(r.redemption_id) AS redemptions, COALESCE(SUM(r.discount_amount), 0) AS total_discount
FROM coupons c
LEFT JOIN coupon_redemptions r ON r.code = c.code
GROUP BY c.code

Another way

SELECT c.code,
       (SELECT COUNT(*) FROM coupon_redemptions r WHERE r.code = c.code) AS redemptions,
       (SELECT IFNULL(SUM(discount_amount), 0) FROM coupon_redemptions r WHERE r.code = c.code) AS total_discount
FROM coupons c

Another way

SELECT c.code, COALESCE(t.n, 0) AS redemptions, COALESCE(t.amt, 0) AS total_discount
FROM coupons c LEFT JOIN (SELECT code, COUNT(*) AS n, SUM(discount_amount) AS amt FROM coupon_redemptions GROUP BY code) t ON t.code = c.code

← B2B Invoices With a Malformed GSTIN · Marketplace Orders per Month in 2024 →