Amazon: Suggested Subscribe & Save Cadence From Reorder Gaps

Hard
596.

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

ColumnTypeValues
order_idint
customer_idint
asinvarchar
ordered_ondate
statusenumdelivered, cancelled, returned

Primary key: order_id.

One row per order of a single product.

Example

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

ConsumableOrder

order_idcustomer_idasinordered_onstatus
151B0DETERG012025-01-05delivered
251B0DETERG012025-02-03delivered
351B0DETERG012025-03-08delivered
451B0DETERG012025-03-08delivered
552B0RAZOR0022025-01-01delivered
652B0RAZOR0022025-02-15delivered
752B0RAZOR0022025-04-01delivered
853B0PETFOOD32025-01-10delivered
953B0PETFOOD32025-02-10cancelled
1053B0PETFOOD32025-03-10delivered
3 more rows

Output

customer_idasinordersspan_dayscadence_months
51B0DETERG013621
52B0RAZOR0023901
54B0VITAMIN432574
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: 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

ColumnType
order_idint
customer_idint
asinvarchar
ordered_ondate
statusenum(delivered, cancelled, returned)

Primary key: order_id.

One row per order of a single product.

Examples

Example 1

ConsumableOrder

order_idcustomer_idasinordered_onstatus
151B0DETERG012025-01-05delivered
251B0DETERG012025-02-03delivered
351B0DETERG012025-03-08delivered
451B0DETERG012025-03-08delivered
552B0RAZOR0022025-01-01delivered
652B0RAZOR0022025-02-15delivered
752B0RAZOR0022025-04-01delivered
853B0PETFOOD32025-01-10delivered
953B0PETFOOD32025-02-10cancelled
1053B0PETFOOD32025-03-10delivered
1154B0VITAMIN42025-01-01delivered
1254B0VITAMIN42025-04-30delivered
1354B0VITAMIN42025-09-15delivered

Output

customer_idasinordersspan_dayscadence_months
51B0DETERG013621
52B0RAZOR0023901
54B0VITAMIN432574

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

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