Amazon: Subscribe & Save Discount Tier per Delivery Month

Easy
579.

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

ColumnTypeValues
delivery_idint
customer_idint
delivery_monthchar
asinvarchar
statusenumdelivered, 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.

Example

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

SnsDelivery

delivery_idcustomer_iddelivery_monthasinstatus
172025-05B0DIAPER01delivered
272025-05B0WIPES002delivered
372025-05B0COFFEE03delivered
472025-05B0SOAP0004delivered
572025-05B0TEA00005delivered
672025-06B0DIAPER01delivered
772025-06B0WIPES002skipped
882025-05B0COFFEE03delivered
982025-05B0TEA00005skipped
1092025-05B0SOAP0004cancelled

Output

customer_iddelivery_monthitemsdiscount_pct
72025-05515
72025-0615
82025-0515
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.

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

ColumnType
delivery_idint
customer_idint
delivery_monthchar
asinvarchar
statusenum(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_idcustomer_iddelivery_monthasinstatus
172025-05B0DIAPER01delivered
272025-05B0WIPES002delivered
372025-05B0COFFEE03delivered
472025-05B0SOAP0004delivered
572025-05B0TEA00005delivered
672025-06B0DIAPER01delivered
772025-06B0WIPES002skipped
882025-05B0COFFEE03delivered
982025-05B0TEA00005skipped
1092025-05B0SOAP0004cancelled

Output

customer_iddelivery_monthitemsdiscount_pct
72025-05515
72025-0615
82025-0515

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_month

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