Products most often bought together

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Self Joins. About 16 minutes. Part of the Pro drill bank.

basket_items contains order line items with columns order_id and product_name. Find the top 5 most frequently co-purchased product pairs. Each pair must be unordered (product_a < product_b) so that (Bread, Butter) is counted identically to (Butter, Bread). Return product_a, product_b, and times_bought_together, ordered by times_bought_together descending, then product_a ascending, and product_b ascending. Limit the result to 5 rows.

Requirements

• Output columns: product_a, product_b, times_bought_together.
• Order by times_bought_together DESC, product_a ASC, product_b ASC.
• Limit to 5 rows.

Constraints

• An order does not contain duplicate rows for the same product.
• Pairs must have product_a < product_b.

Examples

Input: basket_items order_id,product_name 101,Bread 101,Butter 101,Milk 102,Bread 102,Butter 103,Bread 103,Milk ... Output: product_a | product_b | times_bought_together Bread | Butter | 3 Bread | Milk | 2 Butter | Eggs | 1 Butter | Milk | 1 Why this passes: Bread and Butter appear together in orders 101, 102 and 105, so 3; Bread and Milk in orders 101 and 103, so 2; the remaining pairs appear once.

Input: basket_items order_id | product_name 1 | Apple 1 | Banana 2 | Apple 2 | Banana Output: product_a | product_b | times_bought_together Apple | Banana | 2 Why this passes: Apple and Banana appear together in 2 orders.

Topics: lakebench, sql, pairs, market-basket.

More SQL interview questions · All interview problems · Learn data engineering