Amazon: Subscribe & Save Discount Tier per Delivery Month
In this exercise's version of Subscribe & Save, a customer gets 15% off every subscription item in a month when 5 or more of their subscription items are…
- Difficulty: Easy
- Topics: Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #579
Problem statement
In this exercise's version of Subscribe & Save, a customer gets 15% off every subscription item in a month when 5 or more of their subscription items are delivered that month, and 5% otherwise.
For every customer and month with at least one delivered item, return customer_id, delivery_month, items (the delivered items that month) and discount_pct (15 or 5). Skipped and cancelled deliveries do not count towards the five. Return the rows in any order.
A practice scenario set at Amazon: the tables and data are invented, and this is not a question from, or endorsed by, Amazon.
Tables
Table: SnsDelivery
| Column | Type |
|---|---|
| delivery_id | int |
| customer_id | int |
| delivery_month | char |
| asin | varchar |
| status | enum(delivered, skipped, cancelled) |
Primary key: delivery_id.
One row per subscribed item per month, delivery_month as 'YYYY-MM'. A customer has at most one row per item per month.
Examples
Example 1
SnsDelivery
| delivery_id | customer_id | delivery_month | asin | status |
|---|---|---|---|---|
| 1 | 7 | 2025-05 | B0DIAPER01 | delivered |
| 2 | 7 | 2025-05 | B0WIPES002 | delivered |
| 3 | 7 | 2025-05 | B0COFFEE03 | delivered |
| 4 | 7 | 2025-05 | B0SOAP0004 | delivered |
| 5 | 7 | 2025-05 | B0TEA00005 | delivered |
| 6 | 7 | 2025-06 | B0DIAPER01 | delivered |
| 7 | 7 | 2025-06 | B0WIPES002 | skipped |
| 8 | 8 | 2025-05 | B0COFFEE03 | delivered |
| 9 | 8 | 2025-05 | B0TEA00005 | skipped |
| 10 | 9 | 2025-05 | B0SOAP0004 | cancelled |
Output
| customer_id | delivery_month | items | discount_pct |
|---|---|---|---|
| 7 | 2025-05 | 5 | 15 |
| 7 | 2025-06 | 1 | 5 |
| 8 | 2025-05 | 1 | 5 |
How to solve Amazon: Subscribe & Save Discount Tier per Delivery Month
The tier is decided per customer per month, so the query groups by customer_id, delivery_month. Only delivered items count, and filtering them in WHERE before grouping means a month in which everything was skipped or cancelled forms no group at all — exactly the "at least one delivered item" rule.
With the delivered count in hand, CASE WHEN COUNT(*) >= 5 THEN 15 ELSE 5 END assigns the tier. The boundary matters: five delivered items earn the higher discount. Since a customer has at most one row per item per month, counting rows is counting items.
The alternative counts conditionally instead — SUM(CASE WHEN status = 'delivered' …) over all rows — and then has to drop the months whose count is zero, which the WHERE version never creates. Either is a single grouped scan.
Reference solution (MySQL)
SELECT customer_id, delivery_month, COUNT(*) AS items,
CASE WHEN COUNT(*) >= 5 THEN 15 ELSE 5 END AS discount_pct
FROM SnsDelivery
WHERE status = 'delivered'
GROUP BY customer_id, delivery_monthAnother way
SELECT customer_id, delivery_month, items, IF(items >= 5, 15, 5) AS discount_pct FROM (SELECT customer_id, delivery_month, SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS items FROM SnsDelivery GROUP BY customer_id, delivery_month) t WHERE items > 0← Amazon: Alexa Requests and Households by Device Family · Amazon: EC2 Billable Seconds With the One-Minute Minimum →