Write a query to identify products frequently bought together, based on an OrderItems table.

Answer

Self-join OrderItems to itself on shared order_id to find every pair of products that appear together in the same order, then count how often each pair co-occurs.

Solution

SELECT a.product_id AS product_a, b.product_id AS product_b, COUNT(*) AS times_bought_together
FROM OrderItems a
JOIN OrderItems b
  ON a.order_id = b.order_id
  AND a.product_id < b.product_id
GROUP BY a.product_id, b.product_id
ORDER BY times_bought_together DESC;

Step by Step

1. Joining OrderItems to itself on order_id pairs every product in an order with every other product in that same order.
2. a.product_id < b.product_id is the key trick: it keeps each unordered pair exactly once, instead of getting both (A, B) and (B, A) as separate rows, and it also excludes a product being "paired" with itself.
3. Grouping by the pair and counting rows gives how many distinct orders contained both products together.

Adding a Minimum Threshold to Filter Noise

SELECT a.product_id AS product_a, b.product_id AS product_b, COUNT(*) AS times_bought_together
FROM OrderItems a
JOIN OrderItems b ON a.order_id = b.order_id AND a.product_id < b.product_id
GROUP BY a.product_id, b.product_id
HAVING COUNT(*) >= 10
ORDER BY times_bought_together DESC;

Adding a HAVING COUNT(*) >= 10 threshold filters out pairs that only co-occurred once or twice by coincidence, surfacing genuinely meaningful, recurring product associations rather than statistical noise.

Key Exam Traps

• Forgetting a.product_id < b.product_id is the classic mistake in this exact pattern - without it, every pair is counted twice (once in each direction) and each product is also incorrectly "paired" with itself, both of which corrupt the results.
• Large orders create a combinatorial explosion: An order with 20 distinct products generates 190 pairs from that single order alone - on a large orders table, this self-join can produce a very large intermediate result set, worth flagging as a performance consideration.
• Co-occurrence isn't the same as a causal recommendation - two popular products may frequently appear together purely because both are individually popular, not because buying one influences buying the other; a real recommendation engine typically also normalizes against each product's individual frequency (e.g. lift or confidence scores), which is worth mentioning as a natural follow-up.