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

  1. Join order_items to itself on the same order_id: every product paired with every other product in that order.
  2. 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).
  3. Join products twice to get both names.
  4. Group by the pair and count the orders.

Edge cases to mention

Common mistakes

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.