Product co-purchase, customers also bought

Objective

Replicate Amazon's "customers who bought this also bought" widget directly in SQL. With Cypher, the bipartite Customer→Order→Product graph collapses into a single pattern that finds co-purchased items and ranks them by support — useful for upsell, cross-sell, and merchandising rules.

Step 1: Create the graph

// Customers
MERGE (c1:Customer {name: "Sarah Chen"})
MERGE (c2:Customer {name: "Raj Patel"})
MERGE (c3:Customer {name: "Mia Rossi"})
MERGE (c4:Customer {name: "Leo Park"})
MERGE (c5:Customer {name: "Eli Tanaka"})

// Products
MERGE (p1:Product {sku: "ESP-01", name: "Espresso Beans 1kg", price: 24.00})
MERGE (p2:Product {sku: "GRD-02", name: "Burr Grinder",       price: 159.00})
MERGE (p3:Product {sku: "MIL-03", name: "Milk Frother",       price: 49.00})
MERGE (p4:Product {sku: "CUP-04", name: "Ceramic Cup Set",    price: 32.00})
MERGE (p5:Product {sku: "SCL-05", name: "Coffee Scale",       price: 39.00})
MERGE (p6:Product {sku: "FLT-06", name: "Paper Filters",      price: 8.00})

// Orders + line items (CONTAINS edges)
MERGE (o1:Order {id: 1001})-[:PLACED_BY]->(c1)
MERGE (o1)-[:CONTAINS]->(p1)
MERGE (o1)-[:CONTAINS]->(p2)
MERGE (o1)-[:CONTAINS]->(p4)

MERGE (o2:Order {id: 1002})-[:PLACED_BY]->(c2)
MERGE (o2)-[:CONTAINS]->(p1)
MERGE (o2)-[:CONTAINS]->(p2)
MERGE (o2)-[:CONTAINS]->(p5)

MERGE (o3:Order {id: 1003})-[:PLACED_BY]->(c3)
MERGE (o3)-[:CONTAINS]->(p1)
MERGE (o3)-[:CONTAINS]->(p3)

MERGE (o4:Order {id: 1004})-[:PLACED_BY]->(c4)
MERGE (o4)-[:CONTAINS]->(p1)
MERGE (o4)-[:CONTAINS]->(p2)

MERGE (o5:Order {id: 1005})-[:PLACED_BY]->(c5)
MERGE (o5)-[:CONTAINS]->(p1)
MERGE (o5)-[:CONTAINS]->(p6)
MERGE (o5)-[:CONTAINS]->(p5);

Step 2: Customers who bought espresso beans also bought…

// Co-occurrence: walk Order→Product to Order→other Product through the same order.
MATCH (target:Product {sku: "ESP-01"})<-[:CONTAINS]-(o:Order)-[:CONTAINS]->(other:Product)
WHERE other <> target
RETURN other.sku       AS sku,
       other.name      AS also_bought,
       count(o)        AS times_co_purchased
ORDER BY times_co_purchased DESC, sku;

What's happening

• The bipartite hop (target)<-(o)->(other) finds every product sharing an order with the target.
• count(o) produces market-basket support directly — no pivoting, no temp tables.
• Cypher's pattern is symmetric: swap the SKU and you get a different recommender for free.
• Adding more relationship types (e.g. :VIEWED, :WISHLISTED) extends the model without schema migrations — graph databases shine when you keep adding signals over time.
• In SQL this is a self-join on order_items plus aggregation; with millions of rows it gets slow, while the graph version follows pointers and stays fast.

Try this next

MATCH (c:Customer)<-[:PLACED_BY]-(o:Order)-[:CONTAINS]->(p:Product)
RETURN c.name AS customer, sum(p.price) AS lifetime_value
ORDER BY lifetime_value DESC;

MATCH (p:Product)<-[:CONTAINS]-(o:Order)
RETURN p.name AS product, count(o) AS orders
ORDER BY orders DESC LIMIT 5;

MATCH (a:Product)<-[:CONTAINS]-(o:Order)-[:CONTAINS]->(b:Product)
WHERE a.sku < b.sku
RETURN a.name, b.name, count(o) AS support
ORDER BY support DESC LIMIT 10;