BBampaa

Lessons › SQL › Deep Dive: Business Analytics Patterns

SQL lesson

Market basket analysis with self-join

Market basket analysis finds which items most often appear together in the same transaction by joining a table back to itself - matching each row against every other row in the same group.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

Market basket - products bought together

SELECT a.product_id AS product_a, b.product_id AS product_b, COUNT(*) AS pair_count FROM orders a JOIN orders 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 pair_count DESC

Result:

product_aproduct_bpair_count
P2001P20022
P2002P20031

Step by step

  1. Run the query without COUNT so you can see what the unfiltered values look like.
    SELECT a.product_id AS product_a, b.product_id AS product_b, * FROM orders a JOIN orders 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 pair_count DESC;
  2. COUNT tells you how many rows match - perfect for sizing a table.
    SELECT a.product_id AS product_a, b.product_id AS product_b, COUNT(*) FROM orders a JOIN orders 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 pair_count DESC;
  3. AS gives the COUNT result a friendly column name so the output reads cleanly.
    SELECT a.product_id AS product_a, b.product_id AS product_b, COUNT(*) AS pair_count FROM orders a JOIN orders 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 pair_count DESC;

Practice

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

1. On orders, find pairs of customer_id values that share the same order_id - a market-basket self-join. Use customer_id < customer_id to avoid duplicate pairs.
SELECT a.customer_id AS item_a, b.customer_id AS item_b, COUNT(*) AS pair_count FROM orders a JOIN orders b ON a.order_id = b.order_id AND a.customer_id < b.customer_id GROUP BY a.customer_id, b.customer_id ORDER BY pair_count DESC
2. On orders, find pairs of customer_id values that share the same order_id - a market-basket self-join. Use customer_id < customer_id to avoid duplicate pairs.
SELECT a.customer_id AS item_a, b.customer_id AS item_b, COUNT(*) AS pair_count FROM orders a JOIN orders b ON a.order_id = b.order_id AND a.customer_id < b.customer_id GROUP BY a.customer_id, b.customer_id ORDER BY pair_count DESC
3. On orders, find pairs of customer_id values that share the same order_id - a market-basket self-join. Use customer_id < customer_id to avoid duplicate pairs.
SELECT a.customer_id AS item_a, b.customer_id AS item_b, COUNT(*) AS pair_count FROM orders a JOIN orders b ON a.order_id = b.order_id AND a.customer_id < b.customer_id GROUP BY a.customer_id, b.customer_id ORDER BY pair_count DESC
4. On products, find pairs of brand values that share the same product_id - a market-basket self-join. Use brand < brand to avoid duplicate pairs.
SELECT a.brand AS item_a, b.brand AS item_b, COUNT(*) AS pair_count FROM products a JOIN products b ON a.product_id = b.product_id AND a.brand < b.brand GROUP BY a.brand, b.brand ORDER BY pair_count DESC
5. On products, find pairs of brand values that share the same product_id - a market-basket self-join. Use brand < brand to avoid duplicate pairs.
SELECT a.brand AS item_a, b.brand AS item_b, COUNT(*) AS pair_count FROM products a JOIN products b ON a.product_id = b.product_id AND a.brand < b.brand GROUP BY a.brand, b.brand ORDER BY pair_count DESC

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