Amazon: Suggested Subscribe & Save Cadence From Reorder Gaps
The Subscribe & Save team wants to nudge customers who keep reordering the same consumable to subscribe, pre-filled with a delivery cadence that matches…
- Difficulty: Hard
- Topics: Aggregation, Subqueries, Dates, Window Functions
- Dialect: MySQL
- Problem: #596
Problem statement
The Subscribe & Save team wants to nudge customers who keep reordering the same consumable to subscribe, pre-filled with a delivery cadence that matches their habit.
Look at delivered orders only, and count each customer–ASIN pair's distinct order dates (two delivered orders on one day count once). For every pair with at least 3 distinct dates, let orders be that count and span_days the days from the first date to the last. The pair reorders every span_days / (orders − 1) days on average; suggest the cadence cadence_months from 1, 2, 3, 4 or 6 months (a month counted as 30 days) whose length is closest to that average, choosing the shorter cadence on a tie. Return customer_id, asin, orders, span_days and cadence_months, 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: ConsumableOrder
| Column | Type |
|---|---|
| order_id | int |
| customer_id | int |
| asin | varchar |
| ordered_on | date |
| status | enum(delivered, cancelled, returned) |
Primary key: order_id.
One row per order of a single product.
Examples
Example 1
ConsumableOrder
| order_id | customer_id | asin | ordered_on | status |
|---|---|---|---|---|
| 1 | 51 | B0DETERG01 | 2025-01-05 | delivered |
| 2 | 51 | B0DETERG01 | 2025-02-03 | delivered |
| 3 | 51 | B0DETERG01 | 2025-03-08 | delivered |
| 4 | 51 | B0DETERG01 | 2025-03-08 | delivered |
| 5 | 52 | B0RAZOR002 | 2025-01-01 | delivered |
| 6 | 52 | B0RAZOR002 | 2025-02-15 | delivered |
| 7 | 52 | B0RAZOR002 | 2025-04-01 | delivered |
| 8 | 53 | B0PETFOOD3 | 2025-01-10 | delivered |
| 9 | 53 | B0PETFOOD3 | 2025-02-10 | cancelled |
| 10 | 53 | B0PETFOOD3 | 2025-03-10 | delivered |
| 11 | 54 | B0VITAMIN4 | 2025-01-01 | delivered |
| 12 | 54 | B0VITAMIN4 | 2025-04-30 | delivered |
| 13 | 54 | B0VITAMIN4 | 2025-09-15 | delivered |
Output
| customer_id | asin | orders | span_days | cadence_months |
|---|---|---|---|---|
| 51 | B0DETERG01 | 3 | 62 | 1 |
| 52 | B0RAZOR002 | 3 | 90 | 1 |
| 54 | B0VITAMIN4 | 3 | 257 | 4 |
How to solve Amazon: Suggested Subscribe & Save Cadence From Reorder Gaps
There are three steps. First, reduce the orders to distinct delivered dates per customer and ASIN, so a duplicate order on one day does not count as a reorder with a zero-day gap; cancelled and returned orders never happened from the habit's point of view. Grouping those gives orders and span_days = DATEDIFF(last, first), and HAVING COUNT(*) >= 3 keeps pairs with at least two gaps — enough to call it a habit.
Second, the candidate cadences are a five-row inline table built with UNION ALL. Cross-joining it with the pairs gives every (pair, cadence) combination, scored by how far the cadence's length is from the average gap. The average is span / (orders − 1); multiplying both sides by orders − 1 compares |span − 30·m·(orders − 1)| instead, which is exact integer arithmetic with the same ordering, so ties are detected exactly.
Third, keep the best cadence per pair: ROW_NUMBER() ordered by the distance and then by m puts the closest first and the shorter one first among equals. The alternative states the same choice as an anti join — keep cadence c1 when no c2 is strictly closer, or equally close and shorter — and compares the decimal averages directly. Both cost five rows per pair after one grouped scan.
Reference solution (MySQL)
WITH d AS (
SELECT DISTINCT customer_id, asin, ordered_on FROM ConsumableOrder WHERE status = 'delivered'
), s AS (
SELECT customer_id, asin, COUNT(*) AS orders, DATEDIFF(MAX(ordered_on), MIN(ordered_on)) AS span_days
FROM d GROUP BY customer_id, asin HAVING COUNT(*) >= 3
), c AS (
SELECT 1 AS m UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 6
), r AS (
SELECT s.customer_id, s.asin, s.orders, s.span_days, c.m,
ROW_NUMBER() OVER (PARTITION BY s.customer_id, s.asin
ORDER BY ABS(s.span_days - 30 * c.m * (s.orders - 1)), c.m) AS rn
FROM s CROSS JOIN c
)
SELECT customer_id, asin, orders, span_days, m AS cadence_months FROM r WHERE rn = 1Another way
WITH s AS (
SELECT customer_id, asin, COUNT(DISTINCT ordered_on) AS orders, DATEDIFF(MAX(ordered_on), MIN(ordered_on)) AS span_days
FROM ConsumableOrder WHERE status = 'delivered' GROUP BY customer_id, asin HAVING COUNT(DISTINCT ordered_on) >= 3
), c AS (
SELECT 1 AS m UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 6
)
SELECT s.customer_id, s.asin, s.orders, s.span_days, c1.m AS cadence_months
FROM s JOIN c c1
WHERE NOT EXISTS (
SELECT 1 FROM c c2
WHERE ABS(s.span_days / (s.orders - 1) - 30 * c2.m) < ABS(s.span_days / (s.orders - 1) - 30 * c1.m)
OR (ABS(s.span_days / (s.orders - 1) - 30 * c2.m) = ABS(s.span_days / (s.orders - 1) - 30 * c1.m) AND c2.m < c1.m))← Amazon: AWS Promotional Credits Applied Month by Month · Amazon: Packing Station Backlog Carried Hour to Hour →