Market Basket Analysis SQL Interview Explained

Anyone interviewing for a role touching e-commerce or retail data should expect some version of this question. It is the SQL entry point into market basket analysis, the same idea behind the well-known customers who bought this also bought feature, and it hinges on one small but easy-to-miss self-join condition.

Why Interviewers Ask This

It tests whether a candidate can self-join a table correctly, whether they understand how to avoid duplicate or mirrored results, and whether they can translate a business question about what sells together into a precise aggregation. It is a favorite at any company with a product catalog and order history.

The Question

Given a table of order line items, find the top five pairs of products most frequently purchased together in the same order.

Schema

CREATE TABLE order_items (
  id INTEGER PRIMARY KEY,
  order_id INTEGER,
  product_id INTEGER
);

Sample Data

order_id	product_id
1001	10
1001	20
1002	10
1002	20
1002	30
1003	20
1003	30

Here, the pair 10 and 20 appears together in orders 1001 and 1002, twice. The pair 20 and 30 appears in 1002 and 1003, also twice.

Step-by-Step Approach

1. Self-join order_items to itself on a matching order_id. This pairs up every product bought in the same order.
2. Add the condition a.product_id less than b.product_id to the join. This single line does two jobs at once: it prevents a product from pairing with itself, and it stops each pair from showing up twice in reversed order.
3. Count distinct orders per pair, not just row count, which matters if the real schema has quantity stored as separate rows.
4. Group, order, and limit to get the top pairs.

SQLite Solution

SELECT
  a.product_id AS product_a,
  b.product_id AS product_b,
  COUNT(DISTINCT a.order_id) AS times_bought_together
FROM order_items a
JOIN order_items 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
LIMIT 5;

PostgreSQL Solution

SELECT
  a.product_id AS product_a,
  b.product_id AS product_b,
  COUNT(DISTINCT a.order_id) AS times_bought_together
FROM order_items a
JOIN order_items 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
LIMIT 5;

This is one of the rare interview questions where the query is identical across engines. It is plain ANSI SQL with no engine-specific functions involved, worth pointing out to an interviewer as a sign of knowing when portability is not even a concern.

Common Mistakes

1. Forgetting a.product_id less than b.product_id. Without it, every pair appears twice in both directions, plus every product pairs with itself, roughly doubling the result set with noise.
2. Using COUNT(*) instead of COUNT(DISTINCT a.order_id). If a real-world schema ever has more than one row per product per order, such as quantity tracked as repeated rows, plain COUNT(*) silently inflates the numbers.
3. Ignoring scale. A self-join like this is roughly quadratic per order. That is fine for baskets of a handful of items in an interview setting, but it is worth mentioning that real production systems typically use dedicated algorithms such as Apriori or FP-Growth at scale, which shows awareness beyond just passing the test case.

What This Technique Is Actually Called

In data science, this pattern is the SQL-level building block of market basket analysis, also called association rule mining, the same family of technique behind frequently bought together and customers who bought this also bought recommendations.

Frequently Asked Questions

Why is a.product_id less than b.product_id needed in the self-join?

It eliminates two problems at once: a product pairing with itself, and each real pair being counted twice in opposite orders. Without it, the result set roughly doubles and includes meaningless self-pairs.

Does this self-join approach scale to millions of orders?

Not efficiently on its own, since it is roughly quadratic per order. It is exactly what interviewers want to see for a SQL question, but production-scale systems typically use dedicated association rule algorithms such as Apriori or FP-Growth instead.

What is the real name for this technique?

Market basket analysis, also called association rule mining, is the analytical technique behind frequently bought together recommendations.

Keep Practicing

Self-joins with inequality conditions are one of those patterns that click instantly once solved once, and feel impossible before that. Practice self-join and retail analytics SQL questions with our SQL practice questions, or explore full scenarios in our case studies.