Apple: Online Orders With Both an iPhone and AirPods
The online store's merchandising team measures how often customers pair a new iPhone with AirPods in the same checkout.
- Difficulty: Easy
- Topics: Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #605
Problem statement
The online store's merchandising team measures how often customers pair a new iPhone with AirPods in the same checkout.
Return the order_id and customer_name of every order that has at least one iPhone line and at least one AirPods line. Orders with only one of the two, or neither, are not listed. Return the rows ordered by order_id.
A practice scenario set at Apple: the tables and data are invented, and this is not a question from, or endorsed by, Apple.
Tables
Table: OnlineOrder
| Column | Type |
|---|---|
| order_id | int |
| customer_name | varchar |
| ordered_on | date |
Primary key: order_id.
One row per checkout on the online store.
Table: OrderLine
| Column | Type |
|---|---|
| order_id | int |
| line_no | int |
| product_family | enum(iPhone, AirPods, iPad, Mac, Watch, Accessory) |
| qty | int |
Primary key: order_id, line_no.
One row per line of an order; an order can hold several lines of one family.
Examples
Example 1
OnlineOrder
| order_id | customer_name | ordered_on |
|---|---|---|
| 1 | Aarav | 2025-04-01 |
| 2 | Diya | 2025-04-01 |
| 3 | Kabir | 2025-04-02 |
| 4 | Meera | 2025-04-02 |
| 5 | Zara | 2025-04-03 |
OrderLine
| order_id | line_no | product_family | qty |
|---|---|---|---|
| 1 | 1 | iPhone | 1 |
| 1 | 2 | AirPods | 1 |
| 2 | 1 | iPhone | 2 |
| 2 | 2 | Accessory | 1 |
| 3 | 1 | AirPods | 1 |
| 3 | 2 | AirPods | 1 |
| 4 | 1 | Mac | 1 |
| 4 | 2 | iPhone | 1 |
| 4 | 3 | AirPods | 2 |
Output
| order_id | customer_name |
|---|---|
| 1 | Aarav |
| 4 | Meera |
How to solve Apple: Online Orders With Both an iPhone and AirPods
No single row answers the question: one line is an iPhone, another is AirPods. Writing WHERE product_family = 'iPhone' AND product_family = 'AirPods' finds nothing. The condition is about the set of lines in an order, so group the lines by order and test the group.
Conditional aggregation counts each family inside the group — SUM(CASE WHEN product_family = 'iPhone' THEN 1 ELSE 0 END) — and HAVING keeps orders where both counts are positive. Several AirPods lines in one order (order 3 in the example) do not make up for a missing iPhone.
Two other shapes work: two EXISTS probes, one per family, which read like the sentence; or keep only iPhone/AirPods lines and require COUNT(DISTINCT product_family) = 2. All are one pass over the order lines with an index on order_id.
Reference solution (MySQL)
SELECT o.order_id, o.customer_name
FROM OnlineOrder o
JOIN OrderLine l ON l.order_id = o.order_id
GROUP BY o.order_id, o.customer_name
HAVING SUM(CASE WHEN l.product_family = 'iPhone' THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN l.product_family = 'AirPods' THEN 1 ELSE 0 END) > 0
ORDER BY o.order_idAnother way
SELECT o.order_id, o.customer_name FROM OnlineOrder o WHERE EXISTS (SELECT 1 FROM OrderLine l WHERE l.order_id = o.order_id AND l.product_family = 'iPhone') AND EXISTS (SELECT 1 FROM OrderLine l WHERE l.order_id = o.order_id AND l.product_family = 'AirPods') ORDER BY o.order_idAnother way
SELECT o.order_id, o.customer_name FROM OnlineOrder o JOIN (SELECT order_id FROM OrderLine WHERE product_family IN ('iPhone', 'AirPods') GROUP BY order_id HAVING COUNT(DISTINCT product_family) = 2) b ON b.order_id = o.order_id ORDER BY o.order_id← Apple: Apple Music Family Plans With Spare Seats · Apple: Daily Share of Checked-In Devices on iOS 18 →