Which products are bought together most often?

Which pairs of products are bought in the same order most often? Join order_lines to itself on the order (the self join from Chapter 8). a.product < b.product keeps each pair once and stops a product pairing with itself. Then count each pair.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

A self join on the order

SELECT a.product AS product_a,
       b.product AS product_b,
       COUNT(*) AS times
FROM order_lines a
JOIN order_lines b
  ON a.order_id = b.order_id AND a.product < b.product
GROUP BY a.product, b.product
ORDER BY times DESC

Result:

product_a	product_b	times
Charger	Earbuds	3
Earbuds	Phone case	2

Step by step

Each pair once.

SELECT a.product AS product_a, b.product AS product_b, COUNT(*) AS times FROM order_lines a JOIN order_lines b ON a.order_id = b.order_id AND a.product < b.product GROUP BY a.product, b.product ORDER BY times DESC

A self join.

SELECT a.product AS product_a, b.product AS product_b, COUNT(*) AS times FROM order_lines a JOIN order_lines b ON a.order_id = b.order_id AND a.product < b.product GROUP BY a.product, b.product ORDER BY times DESC

a.product < b.product.

SELECT a.product AS product_a, b.product AS product_b, COUNT(*) AS times FROM order_lines a JOIN order_lines b ON a.order_id = b.order_id AND a.product < b.product GROUP BY a.product, b.product ORDER BY times DESC

Join, then GROUP BY the pair.

SELECT a.product AS product_a, b.product AS product_b, COUNT(*) AS times FROM order_lines a JOIN order_lines b ON a.order_id = b.order_id AND a.product < b.product GROUP BY a.product, b.product ORDER BY times DESC

Practice

Try each one in your head first, then open the answer.

1. Which pairs of products are bought in the same order most often? Show product_a, product_b and times, most first.

SELECT a.product AS product_a, b.product AS product_b, COUNT(*) AS times FROM order_lines a JOIN order_lines b ON a.order_id = b.order_id AND a.product < b.product GROUP BY a.product, b.product ORDER BY times DESC

2. Which products were bought together with Earbuds, and how often? Show other_product and times.

SELECT b.product AS other_product, COUNT(*) AS times FROM order_lines a JOIN order_lines b ON a.order_id = b.order_id AND a.product <> b.product WHERE a.product = 'Earbuds' GROUP BY b.product

3. How many products are in each order? Show order_id and products.

SELECT order_id, COUNT(*) AS products FROM order_lines GROUP BY order_id

4. Which orders had both Earbuds and a Charger? Show order_id.

SELECT a.order_id FROM order_lines a JOIN order_lines b ON a.order_id = b.order_id WHERE a.product = 'Earbuds' AND b.product = 'Charger'

5. Which pair of products is bought together most often? Show the top pair only: product_a, product_b and times.

SELECT a.product AS product_a, b.product AS product_b, COUNT(*) AS times FROM order_lines a JOIN order_lines b ON a.order_id = b.order_id AND a.product < b.product GROUP BY a.product, b.product ORDER BY times DESC LIMIT 1

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed