Amazon: Echo Buyers Who Added a Smart Plug Within 60 Days

Medium
583.

Amazon: Echo Buyers Who Added a Smart Plug Within 60 Days

The smart-home team measures attach rate: how many Echo buyers go on to build a connected home. The first step they track is a smart plug.

Return the customer_id of every customer who bought an Echo and then a Smart Plug on the same day or up to 60 days after that Echo purchase (60 days inclusive). Purchases that were returned do not count, neither as the Echo nor as the plug. A plug bought before every Echo of the customer does not count. Each customer appears once; 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: DevicePurchase

ColumnTypeValues
purchase_idint
customer_idint
product_lineenumEcho, Smart Plug, Fire TV Stick, Kindle
purchased_ondate
returnedbool

Primary key: purchase_id.

One row per device bought; returned is 1 when the customer sent it back.

Example

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

DevicePurchase

purchase_idcustomer_idproduct_linepurchased_onreturned
121Echo2025-01-100
221Smart Plug2025-03-110
322Echo2025-01-100
422Smart Plug2025-03-120
523Smart Plug2025-01-050
623Echo2025-01-060
724Echo2025-02-011
824Smart Plug2025-02-020
925Echo2025-02-140
1025Smart Plug2025-02-140
1 more row

Output

customer_id
21
25
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: Echo Buyers Who Added a Smart Plug Within 60 Days

The smart-home team measures attach rate: how many Echo buyers go on to build a connected home. The first step they track is a smart plug.

  • Difficulty: Medium
  • Topics: Joins, Subqueries, Dates
  • Dialect: MySQL
  • Problem: #583

Problem statement

The smart-home team measures attach rate: how many Echo buyers go on to build a connected home. The first step they track is a smart plug.

Return the customer_id of every customer who bought an Echo and then a Smart Plug on the same day or up to 60 days after that Echo purchase (60 days inclusive). Purchases that were returned do not count, neither as the Echo nor as the plug. A plug bought before every Echo of the customer does not count. Each customer appears once; 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: DevicePurchase

ColumnType
purchase_idint
customer_idint
product_lineenum(Echo, Smart Plug, Fire TV Stick, Kindle)
purchased_ondate
returnedbool

Primary key: purchase_id.

One row per device bought; returned is 1 when the customer sent it back.

Examples

Example 1

DevicePurchase

purchase_idcustomer_idproduct_linepurchased_onreturned
121Echo2025-01-100
221Smart Plug2025-03-110
322Echo2025-01-100
422Smart Plug2025-03-120
523Smart Plug2025-01-050
623Echo2025-01-060
724Echo2025-02-011
824Smart Plug2025-02-020
925Echo2025-02-140
1025Smart Plug2025-02-140
1125Smart Plug2025-02-200

Output

customer_id
21
25

How to solve Amazon: Echo Buyers Who Added a Smart Plug Within 60 Days

The question pairs two purchases of one customer: an Echo and a later plug. A self join of DevicePurchase — e for the Echo, p for the plug, matched on customer_id — puts every candidate pair on one row; the window is then DATEDIFF(p.purchased_on, e.purchased_on) BETWEEN 0 AND 60. Zero covers the same-day plug, 60 the last day of the window, and a negative difference (plug first) is excluded.

Returned devices are filtered on both sides, since a returned Echo did not start a smart home and a returned plug did not extend one. A customer with two Echos may have a plug that is late for the first but within 60 days of the second — the pairwise join handles that automatically. Because one customer can produce several matching pairs, DISTINCT collapses them to one row.

The alternatives use EXISTS instead of a join, so no duplicates arise in the subquery — one starts from the Echo and looks forward with DATE_ADD, the other starts from the plug and looks back with DATE_SUB. All three are semi joins; with an index on (customer_id, product_line) each purchase probes only that customer's rows.

Reference solution (MySQL)

SELECT DISTINCT e.customer_id
FROM DevicePurchase e
JOIN DevicePurchase p
  ON p.customer_id = e.customer_id
 AND p.product_line = 'Smart Plug' AND p.returned = 0
 AND DATEDIFF(p.purchased_on, e.purchased_on) BETWEEN 0 AND 60
WHERE e.product_line = 'Echo' AND e.returned = 0

Another way

SELECT DISTINCT customer_id FROM DevicePurchase e
WHERE product_line = 'Echo' AND returned = 0 AND EXISTS (
  SELECT 1 FROM DevicePurchase p WHERE p.customer_id = e.customer_id AND p.product_line = 'Smart Plug' AND p.returned = 0
    AND p.purchased_on >= e.purchased_on AND p.purchased_on <= DATE_ADD(e.purchased_on, INTERVAL 60 DAY))

Another way

SELECT customer_id FROM DevicePurchase p WHERE product_line = 'Smart Plug' AND returned = 0
  AND EXISTS (SELECT 1 FROM DevicePurchase e WHERE e.customer_id = p.customer_id AND e.product_line = 'Echo' AND e.returned = 0
              AND e.purchased_on BETWEEN DATE_SUB(p.purchased_on, INTERVAL 60 DAY) AND p.purchased_on)
GROUP BY customer_id

← Amazon: Star Rating Breakdown for Each Product · Amazon: Warehouse Bins Shared by Several Sellers →