Products bought together

Step 1 Solve it yourself first

Tables you will use

order_items products

What it really 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".

Picture it

flowchart LR
  O["order 102<br/>Desk lamp, Backpack"] --> P["self join on order_id<br/>Backpack–Desk lamp<br/>Desk lamp–Backpack<br/>Backpack–Backpack …"]
  P -->|"keep name a less than name b"| K["Backpack–Desk lamp<br/>once"]

The approach, step by step

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.

SELECT p1.product_name AS product_a, p2.product_name AS product_b,
       COUNT(*) AS orders_together
FROM order_items a
JOIN order_items b ON b.order_id = a.order_id
JOIN products p1   ON p1.product_id = a.product_id
JOIN products p2   ON p2.product_id = b.product_id
WHERE p1.product_name < p2.product_name
GROUP BY p1.product_name, p2.product_name
ORDER BY orders_together DESC, product_a, product_b;

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Practice

  DataLemur — frequently bought together questions

  real interview questions, many free

• Practice

  PostgreSQL Exercises — Joins and Subqueries

  pgexercises.com, free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain what the self join produces before the WHERE, for order 102.

   Show answer

   Order 102 has two item lines: product 4 (desk lamp) and product 5 (backpack). Joining order_items to itself on order_id pairs every line of the order with every line of the same order, 2 × 2 = 4 rows: (lamp, lamp), (lamp, backpack), (backpack, lamp), (backpack, backpack).

   Two of those are a product paired with itself, and the other two are the same pair in both orders. WHERE p1.product_name < p2.product_name removes both problems at once: a name is never less than itself, and of (lamp, backpack) and (backpack, lamp) only one has the names in alphabetical order. One row is left: (Backpack, Desk lamp).

   sqlCopy

   SELECT a.order_id, a.product_id AS a_product, b.product_id AS b_product
   FROM order_items a
   JOIN order_items b ON b.order_id = a.order_id
   WHERE a.order_id = 102
   ORDER BY a.product_id, b.product_id;

   Try it

   4 rows

   order_id	a_product	b_product
   102	4	4
   102	4	5
   102	5	4
   102	5	5

2. Writefrom a blank page, then run it

   For each product, list how many different products it has ever been bought with.

   Show answer

   Pair each product's item lines with the other lines of the same order (b.product_id <> a.product_id), and count distinct partners. LEFT JOINs keep the products with no partners: the headphones are often bought alone, and the phone case has never been bought at all.

   sqlCopy

   SELECT p.product_name, COUNT(DISTINCT b.product_id) AS bought_with
   FROM products p
   LEFT JOIN order_items a ON a.product_id = p.product_id
   LEFT JOIN order_items b ON b.order_id = a.order_id AND b.product_id <> a.product_id
   GROUP BY p.product_id, p.product_name
   ORDER BY bought_with DESC, p.product_name;

   Try it

   7 rows

   product_name	bought_with
   Backpack	2
   Desk lamp	2
   Mug	2
   Notebook	2
   Headphones	1
   Pen	1
   Phone case	0

3. Predictsay it before you run it

   How many rows does the self join order_items a JOIN order_items b ON b.order_id = a.order_id produce before any WHERE?

   Show answer

   24. Each order contributes (its number of item lines)², because every line is paired with every line of the same order, itself included. Orders 101, 104, 107 and 108 have 1 line each (1 row each); orders 102, 103, 105, 109 and 110 have 2 lines each (4 rows each). 4 × 1 + 5 × 4 = 24.

   sqlCopy

   SELECT COUNT(*) AS pairs_before_where
   FROM order_items a
   JOIN order_items b ON b.order_id = a.order_id;

   Try it

   1 row

   pairs_before_where
   24

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   If an order could contain the same product on two rows, how would you stop it pairing with itself and double-counting?

   Show answer

   Two separate problems, two fixes:

   • Self-pairs: compare product ids, not rows. a.product_id < b.product_id can never pair a product with itself, even if it appears on two rows of the order (two lines with the same product have equal ids, so < is false).
   • Double counting: the same pair would come out several times for one order (2 lines × 1 line = 2 rows). Count orders, not rows: COUNT(DISTINCT a.order_id). Or remove the duplicates first, in a CTE: SELECT DISTINCT order_id, product_id FROM order_items, then self-join that.

   In this database it can't happen, because the primary key (order_id, product_id) allows one row per product per order. But many real order tables have no such key.

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

I self-join the order items table on order_id so every product is paired with every other product in the same order, filter with product a less than product b to drop self-pairs and mirrored duplicates, join product names, and count orders per pair. I'd note that the number of pairs grows quadratically with basket size, so on big data I might limit to recent orders or popular products.

Follow-up questions

Tick each check when you can do it without notes