Intermediate
Case 26 of 50
Case 026: Bought Together
order_itemsproducts
Marketing wants a "customers who bought X also bought Y" feature, and the manager passes along the ask.
"Could you find out which products actually get bought together, in the same order?"
The data Alex is looking at
order_items
| order_id | product_id | quantity |
|---|---|---|
| 101 | 1 | 2 |
| 101 | 2 | 1 |
| 102 | 3 | 1 |
| 103 | 2 | 3 |
| 104 | 5 | 1 |
| … 29 more rows | ||
products
| id | name | category | price |
|---|---|---|---|
| 1 | Keyboard | Accessories | 2500 |
| 2 | Mouse | Accessories | 1200 |
| 3 | Headphones | Audio | 3500 |
| 4 | Monitor | Displays | 12000 |
| 5 | Laptop | Computers | 60000 |
| … 5 more rows | |||
TASK
Help Alex answer the manager:
“Find every pair of products that have appeared together in the same order, with how many times each pair occurred.”
Write a query above and hit Run to see what comes back.
No hints requested yet — click "Get a hint" when you're stuck.
Alex's final query — this is for reference. Copy it into your own thinking, not into the editor above.
SELECT
p1.name AS product_a,
p2.name AS product_b,
COUNT(*) AS times_together
FROM order_items a
JOIN order_items b
ON a.order_id = b.order_id AND a.product_id < b.product_id
JOIN products p1 ON p1.id = a.product_id
JOIN products p2 ON p2.id = b.product_id
GROUP BY p1.name, p2.name
ORDER BY times_together DESC;
What that query returns
Case Debrief
Self-JOIN
- A self-join compares rows from a table to other rows in that same table — the a.id < b.id trick avoids duplicate and self-paired rows.