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
| Column | Type |
|---|---|
| code | varchar |
| discount_type | enum(flat, percent) |
| valid_till | date |
Primary key: code.
One row per coupon the marketing team created.
Table: coupon_redemptions
| Column | Type |
|---|---|
| redemption_id | int |
| code | varchar |
| order_id | int |
| discount_amount | int |
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
| code | discount_type | valid_till |
|---|---|---|
| WELCOME100 | flat | 2024-12-31 |
| FESTIVE20 | percent | 2024-11-15 |
| UPI50 | flat | 2024-12-31 |
| DIWALI500 | flat | 2024-11-05 |
coupon_redemptions
| redemption_id | code | order_id | discount_amount |
|---|---|---|---|
| 1 | WELCOME100 | 8101 | 100 |
| 2 | FESTIVE20 | 8102 | 340 |
| 3 | WELCOME100 | 8103 | 100 |
| 4 | FESTIVE20 | 8104 | 1200 |
| 5 | UPI50 | 8105 | 50 |
| 6 | FESTIVE20 | 8106 | 85 |
Output
| code | redemptions | total_discount |
|---|---|---|
| DIWALI500 | 0 | 0 |
| FESTIVE20 | 3 | 1625 |
| UPI50 | 1 | 50 |
| WELCOME100 | 2 | 200 |
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.codeAnother 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 cAnother 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 →