SQL interview question: Products Frequently Bought Together
Frequently Bought Together
The recommendation engine needs to find product pairs frequently purchased in the same order.
Find all product pairs that were purchased together in the same order at least twice.
Return product_1, product_2 (product names), and times_bought_together. Self-join order_items on order_id.
Skills: self-join with aggregation
Tables
order_items 19 rows
- id integer PK
- order_id integer → orders
- product_id integer → products
- quantity integer
| id | order_id | product_id | quantity |
|---|---|---|---|
| 1 | 1 | 3 | 1 |
| 2 | 2 | 1 | 1 |
| 3 | 2 | 6 | 1 |
products 20 rows
- product_id integer PK
- product_name text
- category text
- price real
- rating real
| product_id | product_name | category | price | rating |
|---|---|---|---|---|
| 1 | MacBook Pro | Electronics | 1999.99 | 4.8 |
| 2 | iPad Air | Electronics | 599.99 | 4.8 |
| 3 | AirPods Pro | Electronics | 249.99 | 4.7 |
Practice on your iPhone
Query Dojo for iPhone has 85 interview questions, timed mock interviews and a tile builder, so you can write queries without a keyboard.