Market Basket Analysis - Most Frequently Co-Purchased Products
Preview mode. Log in to edit, run, submit, and save progress.
Description
You are given a Purchases table where each row represents one product in one order. Multiple products can appear in the same order. Write a SQL query to find all pairs of products that were purchased together in the same order, and count how many times each pair appeared. Only report pairs where product_1 < product_2 (lexicographic order) to avoid duplicates. Return product_1, product_2, and times_bought_together, ordered by times_bought_together descending, then product_1, then product_2. Table: Purchases
| Column Name | Type | Description |
|---|---|---|
| order_id | INT | ID of the order |
| product | VARCHAR | Name or ID of the product |
Database Schema (Inferred)
Purchases
| Column Name | Example Value |
|---|---|
| order_id | 1 |
| product | A |
Example
Purchases
| order_id | product |
|---|---|
| 1 | A |
| 1 | B |
| 1 | C |
| 2 | A |
| 2 | B |
| 3 | B |
| 3 | C |
| 4 | A |
| 4 | B |
| 4 | C |
| 5 | A |
| 5 | C |
Output
| product_1 | product_2 | times_bought_together |
|---|---|---|
| A | B | 3 |
| A | C | 3 |
| B | C | 3 |
Explanation:
A+B appear in orders 1,2,4 (3 times). A+C appear in orders 1,4,5 (3 times). B+C appear in orders 1,3,4 (3 times).
Approach hint
Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.
Common mistake
Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.
Purchases
| order_id | product |
|---|---|
| 1 | A |
| 1 | B |
| 1 | C |
| 2 | A |
| 2 | B |
| 3 | B |
| 3 | C |
| 4 | A |
| 4 | B |
| 4 | C |
| 5 | A |
| 5 | C |
Output
| product_1 | product_2 | times_bought_together |
|---|---|---|
| A | B | 3 |
| A | C | 3 |
| B | C | 3 |