Products bought together
Find every pair of different products that appear in the same order, and count how many orders contain each pair. List each pair once.
What it tests
A self join on a link table, and the trick for listing each pair once — the basis of "customers who bought this also bought…".
The pattern
Pairs within a group (self join with a < b). "Bought together", "pairs", "in the same order / session / team", "co-occurrence".
How to approach it
- Join order_items to itself on the same order_id: every product paired with every other product in that order.
- Keep only pairs where the first product's name is less than the second's — this removes self-pairs and mirrored duplicates (A–B and B–A).
- Join products twice to get both names.
- Group by the pair and count the orders.
Edge cases to mention
- The same product twice in one order: order_items has one row per product per order, so it can't pair with itself here — but say how you would handle it.
- Big orders make many pairs (n items → n × (n − 1) / 2 pairs), so this can get expensive on real data.
- Pairs vs. lift: "bought together most" favours popular products; analysts often normalise by each product's own popularity.
Common mistakes
- Using
<>instead of<, which lists every pair twice. - Joining on product_id instead of order_id, which pairs a product with itself across orders.
- Counting rows when an order could contain a pair more than once — use
COUNT(DISTINCT a.order_id)if so.
Not true: Joining the item rows with a.product_id <> b.product_id lists each pair of products once.
In fact: <> gives every pair twice: (Pen, Mug) and (Mug, Pen). Use < so that each pair appears only once.
Part of Unit 8: SQL interview problems, from easy to hard in the free SQL course.