Immediate AirPods Follow-Up RateCompany: Apple | Difficulty: hard

Apple's retention team needs the follow-up rate for customers who buy AirPods immediately after an iPhone.

Write a query that returns follow_up_percentage: the percentage of customers with at least one valid iPhone purchase whose immediately next valid purchase after any iPhone purchase is AirPods. Count each qualifying customer once. A valid purchase has a non-NULL product_name; ignore rows with a NULL product_name before determining purchase order. Treat exact duplicate rows as one purchase. Within each customer_id, order valid purchases by purchased_at ascending, then purchase_id ascending. Round to the nearest whole percentage; return 0 if no customer has a valid iPhone purchase. Order by follow_up_percentage descending.

Table Schema

Table	Column	Type
`purchase_history`	`purchase_id`	integer
`purchase_history`	`customer_id`	integer
`purchase_history`	`product_name`	varchar
`purchase_history`	`purchased_at`	timestamp

Example Input: `purchase_history`

`purchase_id`	`customer_id`	`product_name`	`purchased_at`
`101`	`901`	`iPhone`	`2025-01-08 09:15:00`
`102`	`901`	`AirPods`	`2025-01-12 18:30:00`
`103`	`902`	`iPhone`	`2025-02-03 10:00:00`
`104`	`902`	`iPad`	`2025-02-05 11:45:00`
`105`	`902`	`AirPods`	`2025-02-09 14:20:00`
`106`	`903`	`MacBook`	`2025-02-11 16:00:00`

Example Output Explanation

Customer 901 qualifies because their next valid purchase after iPhone is AirPods. Customer 902 does not qualify because iPad occurs between iPhone and AirPods. Customer 903 is excluded from the denominator because they did not purchase iPhone.

SchemaDROP TABLE IF EXISTS purchase_history; CREATE TABLE purchase_history (purchase_id INT, customer_id INT, product_name VARCHAR(50), purchased_at TIMESTAMP); INSERT INTO purchase_history VALUES (101, 901, 'iPhone', '2025-01-08 09:15:00'), (102, 901, 'AirPods', '2025-01-12 18:30:00'), (103, 902, 'iPhone', '2025-02-03 10:00:00'), (104, 902, 'iPad', '2025-02-05 11:45:00'), (105, 902, 'AirPods', '2025-02-09 14:20:00'), (106, 903, 'MacBook', '2025-02-11 16:00:00');← All SQL Challenges · Apple Questions · Learn SQL Free