Products Frequently Bought Together

Problem

Schema: OrderItem(order_id INT, product_id INT). Each row is one product in an order. Return the pairs (a, b) with a < b and the number of orders that contain both, ordered by count desc, then a, then b.

Constraints

• No duplicate (order_id, product_id) within a single order.

Approach — Core Patterns

This is a Core Patterns problem. The idea: pick the data structure best matched to the constraints and solve it in one clean pass. Work through the reference code below line by line, then re-derive it yourself in the editor — that's how the pattern sticks.

Complexity: see the walkthrough below.

Solution code

SQL

SELECT o1.product_id AS a, o2.product_id AS b, COUNT(*) AS orders
FROM OrderItem o1
JOIN OrderItem o2
  ON o2.order_id = o1.order_id
 AND o2.product_id > o1.product_id
GROUP BY o1.product_id, o2.product_id
ORDER BY orders DESC, a, b;

Practice it

Reading a solution isn't the same as being able to write it under pressure. Open this problem in the in-browser editor, hide the solution, and solve it from scratch — your code runs against real test cases instantly.