Apple: Online Orders With Both an iPhone and AirPods

Easy
605.

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.

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

ColumnType
order_idint
customer_namevarchar
ordered_ondate

Primary key: order_id.

One row per checkout on the online store.

Table: OrderLine

ColumnTypeValues
order_idint
line_noint
product_familyenumiPhone, AirPods, iPad, Mac, Watch, Accessory
qtyint

Primary key (together): order_id, line_no.

One row per line of an order; an order can hold several lines of one family.

Example

The rows must come back in the order shown.

OnlineOrder

order_idcustomer_nameordered_on
1Aarav2025-04-01
2Diya2025-04-01
3Kabir2025-04-02
4Meera2025-04-02
5Zara2025-04-03

OrderLine

order_idline_noproduct_familyqty
11iPhone1
12AirPods1
21iPhone2
22Accessory1
31AirPods1
32AirPods1
41Mac1
42iPhone1
43AirPods2

Output

order_idcustomer_name
1Aarav
4Meera
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.

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

ColumnType
order_idint
customer_namevarchar
ordered_ondate

Primary key: order_id.

One row per checkout on the online store.

Table: OrderLine

ColumnType
order_idint
line_noint
product_familyenum(iPhone, AirPods, iPad, Mac, Watch, Accessory)
qtyint

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_idcustomer_nameordered_on
1Aarav2025-04-01
2Diya2025-04-01
3Kabir2025-04-02
4Meera2025-04-02
5Zara2025-04-03

OrderLine

order_idline_noproduct_familyqty
11iPhone1
12AirPods1
21iPhone2
22Accessory1
31AirPods1
32AirPods1
41Mac1
42iPhone1
43AirPods2

Output

order_idcustomer_name
1Aarav
4Meera

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_id

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

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