Market Basket Analysis in SQL: What Do Customers Buy Together?

"Customers who bought X also bought Y."

We see this everywhere, from Amazon recommendations to Netflix "Because you watched..." lists. This is known as Market Basket Analysis (or Association Rule Mining).

While serious data scientists use algorithms like Apriori or FP-Growth in Python, you can get 90% of the value using simple SQL to find frequent item pairs.

A Real Business Scenario: Which Product Pairings Deserve a Merchandising Test?

Retail and ecommerce teams rarely start with "build a recommendation model." They start with a narrower question:

• which items are commonly bought together?
• which bundles deserve a landing page or promo slot?
• which pairings should sales or lifecycle emails surface?

SQL market-basket analysis is useful because it gives a fast first answer from transactional tables you already have. You can identify obvious co-purchase patterns before you invest in more advanced recommendation systems.

The Core Concept: Self-Joins

To find items bought together, we need to look at the order_items table. Specifically, we want to find two different items that appear in the same order_id.

This requires a Self-Join: joining the table to itself.

SELECT
    a.product_id as product_A,
    b.product_id as product_B,
    COUNT(*) as frequency
FROM order_items a
JOIN order_items b 
    ON a.order_id = b.order_id     -- Same Order
    AND a.product_id < b.product_id -- Different Products (avoid mirrors)
GROUP BY product_A, product_B
ORDER BY frequency DESC;

Why a.product_id < b.product_id? If we just used !=, we would get pairs twice: (Bread, Milk) and (Milk, Bread). Using < forces a specific order, ensuring we count the pair only once.

A Common Mistake: Treating Raw Pair Counts as Enough

A pair with COUNT(*) = 50 can be interesting or meaningless depending on scale.

If Product A appears in 60 orders total, 50 shared orders is huge. If it appears in 50,000 orders, that same count is weak.

This is why raw frequency alone should not be the final decision metric. At minimum, analysts usually compare:

• pair frequency
• support relative to all orders
• confidence relative to the base item frequency

Without that context, you risk over-prioritizing popular items that appear in many baskets regardless of any meaningful association.

Calculating Confidence

Knowing that "Bread and Milk" were bought together 500 times is useful. But is that high? If there are 1,000,000 orders, 500 is nothing. If there are 600 orders, it's huge.

We usually calculate Confidence: When a customer buys Product A, how likely are they to buy Product B?

Formula:

Confidence(A -> B) = (Orders containing A and B) / (Orders containing A)

Confidence is directional, which matters.

• Confidence(Diapers -> Beer) may be high
• Confidence(Beer -> Diapers) may be much lower

Those are not the same business statement, because the denominator changes.

Interactive Playground

Let's find the most popular product pairings in our grocery store data.

Taking It Further: The "Combo Deal"

Once you identify these pairs (e.g., "Beer and Diapers"), you can take action:

1. Product Placement: Put them next to each other on the shelf (or web page).
2. Bundling: Create a "Weekend Warrior Pack" with both items.
3. Recommendations: "You added Diapers to your cart. Don't forget the Beer!"

Boundary and Performance Notes

Self-join pair generation grows quickly as basket size increases.

• Large orders create many pair combinations, which can inflate compute cost fast.
• Duplicate line items in the same order can distort counts if you do not normalize to one row per (order_id, product_id) first.
• Very common products often dominate pair counts, so ranking only by frequency can hide more interesting niche associations.
• For larger catalogs or higher-order combinations, purpose-built association-rule tooling may outperform ad hoc SQL.

SQL pair analysis is strongest as a practical first pass: fast enough to reveal obvious patterns, interpretable enough to discuss with product or merchandising teams, and easy to validate against raw orders.

When NOT to Use Simple Pair Analysis

Avoid over-reading simple co-purchase counts when:

• the catalog is tiny and every item co-occurs with everything else
• the product question is sequential behavior, not same-basket behavior
• you need personalized recommendations rather than broad catalog-level associations

In those cases, the market-basket frame may not match the actual decision problem. A funnel, sequence, or user-level affinity model may be more appropriate.

Official References

• Google BigQuery self-join best practices for a general warehouse perspective on expensive join patterns and scaling considerations.
• Microsoft association rules algorithm overview for a practical reference on support, confidence, and association-rule interpretation.
• PostgreSQL table expressions and joins for formal join semantics behind the self-join pattern.

Conclusion

You don't always need complex machine learning models to build a recommendation engine. A clever SQL self-join is often enough to uncover the strongest relationships in your data.

Related Articles

• Mastering SQL Self Joins for the table-to-itself join pattern behind basket analysis.
• Building Conversion Funnels in SQL for another user-behavior analysis pattern that turns raw events into actionable structure.
• SQL Ecommerce Analytics: Real Reporting Queries for practical commerce analysis contexts where product-pair insights can be applied.