E-commerce recommendation engines need to know which items customers buy in the same transaction so they can surface "frequently bought together" suggestions. Identifying high co-occurrence pairs lets the merchandising team bundle products, optimize shelf placement, and trigger targeted upsells. Using the order_items table, return product_a, product_b, and co_occurrence_count for every pair of distinct products that appeared in the same order at least once, ordered by co_occurrence_count descending then product_a, product_b ascending. Ensure each pair (A, B) appears once (A < B).
order_items
| column | type |
|---|---|
| id | INTEGER |
| order_id | INTEGER |
| product_id | INTEGER |
| quantity | INTEGER |
order_items
| id | order_id | product_id | quantity |
|---|---|---|---|
| 1 | 1 | 1 | 1 |
| 2 | 1 | 2 | 1 |
| 3 | 2 | 1 | 2 |
| 4 | 2 | 2 | 1 |
| 5 | 3 | 2 | 1 |
| 6 | 3 | 3 | 1 |
| product_a | product_b | co_occurrence_count |
|---|---|---|
| 1 | 2 | 2 |
| 2 | 3 | 1 |
Products 1 and 2 appear together in orders 1 and 2 (count = 2). Products 2 and 3 appear together only in order 3 (count = 1).