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
| Column | Type |
|---|---|
| purchase_id | int |
| customer_id | int |
| product_line | enum(Echo, Smart Plug, Fire TV Stick, Kindle) |
| purchased_on | date |
| returned | bool |
Primary key: purchase_id.
One row per device bought; returned is 1 when the customer sent it back.
Examples
Example 1
DevicePurchase
| purchase_id | customer_id | product_line | purchased_on | returned |
|---|---|---|---|---|
| 1 | 21 | Echo | 2025-01-10 | 0 |
| 2 | 21 | Smart Plug | 2025-03-11 | 0 |
| 3 | 22 | Echo | 2025-01-10 | 0 |
| 4 | 22 | Smart Plug | 2025-03-12 | 0 |
| 5 | 23 | Smart Plug | 2025-01-05 | 0 |
| 6 | 23 | Echo | 2025-01-06 | 0 |
| 7 | 24 | Echo | 2025-02-01 | 1 |
| 8 | 24 | Smart Plug | 2025-02-02 | 0 |
| 9 | 25 | Echo | 2025-02-14 | 0 |
| 10 | 25 | Smart Plug | 2025-02-14 | 0 |
| 11 | 25 | Smart Plug | 2025-02-20 | 0 |
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 = 0Another 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 →