Recommendation engines surface "customers also bought" suggestions by finding which products co-occur most frequently in the same transaction. Pairs that appear together only once could be coincidence, but pairs that appear together repeatedly signal a genuine purchasing pattern worth promoting. The recommendation engine needs to know which products are commonly bought in the same order. Using the order_items and products tables, find every pair of products that appear together in more than one order — return product_1, product_2, and times_bought_together, ordered by frequency descending then by product_1.
products
| column | type |
|---|---|
| id | INTEGER |
| name | TEXT |
| category | TEXT |
| price | NUMERIC |
order_items
| column | type |
|---|---|
| id | INTEGER |
| order_id | INTEGER |
| product_id | INTEGER |
| quantity | INTEGER |
| unit_price | NUMERIC |
products
| id | name | category | price |
|---|---|---|---|
| 1 | Widget | Tools | 9.99 |
| 2 | Gadget | Electronics | 149.99 |
| 3 | Gizmo | Electronics | 79.99 |
order_items
| id | order_id | product_id | quantity | unit_price |
|---|---|---|---|---|
| 1 | 101 | 1 | 1 | 9.99 |
| 2 | 101 | 2 | 1 | 149.99 |
| 3 | 101 | 3 | 1 | 79.99 |
| 4 | 102 | 1 | 2 | 9.99 |
| 5 | 102 | 3 | 1 | 79.99 |
| 6 | 103 | 1 | 1 | 9.99 |
| 7 | 103 | 2 | 2 | 149.99 |
| product_1 | product_2 | times_bought_together |
|---|---|---|
| Widget | Gadget | 2 |
| Widget | Gizmo | 2 |
Widget+Gadget appear together in orders 101 and 103 (count = 2). Widget+Gizmo appear together in orders 101 and 102 (count = 2). Both pairs clear the HAVING COUNT(*) > 1 threshold. Gadget+Gizmo appear together only in order 101 (count = 1) and are excluded. Both qualifying pairs have count = 2 and tie on frequency, so they are ordered by product_1 name ascending.