Part 179: Market Basket Analysis: Co-Occurrence & Association in SQL

Discovering "Customers who bought Item A also bought Item B" using self-joins.

1. The Co-Occurrence Problem

Given millions of shopping orders, how do we identify pairs of products that appear in the same cart most frequently?

2. The Self-Join Co-Occurrence Pattern

SELECT 
    p1.product_name AS product_a,
    p2.product_name AS product_b,
    COUNT(DISTINCT oi1.order_id) AS times_bought_together
FROM order_items oi1
-- Self-join on the same order_id
JOIN order_items oi2 
    ON oi1.order_id = oi2.order_id 
    -- CRITICAL: '<' prevents pairing item with itself (A,A) 
    -- and avoids duplicate mirror pairs (A,B and B,A)
    AND oi1.product_id < oi2.product_id
JOIN products p1 ON oi1.product_id = p1.id
JOIN products p2 ON oi2.product_id = p2.id
GROUP BY p1.product_name, p2.product_name
ORDER BY times_bought_together DESC
LIMIT 20;

3. Calculating Support and Confidence

• Support: $P(A \cap B) = \frac{\text{Orders with both A and B}}{\text{Total Orders}}$
• Confidence: $P(B | A) = \frac{\text{Orders with both A and B}}{\text{Orders with A}}$

Key Takeaways

• Always use oi1.product_id < oi2.product_id in association self-joins to eliminate self-matches and cut permutations in half.

In Part 180, we explore Time-Series Gap Filling, Interpolation & Rolling Volatility!