Bundled Items
ASKED IN INTERVIEWAmazon
Problem Statement
Amazon's recommendation engine needs to know which products have a high affinity for one another. A bundle is defined as any two unique products purchased within the same order.
Write an SQL query to find the top 5 most frequent product pairs.
Rules:
- A pair consists of product_a and product_b.
- To avoid duplicates, product_a must always be strictly less than product_b (alphabetically or numerically).
- Output columns: product_a, product_b, bundle_count.
- Results must be returned in descending order of bundle_count.
- Only return the top 5 results.
Table Schema:
- OrderItems: order_id, product_id.
Tests your understanding of
Self Join, Aggregation, Filtering and Market Basket Analysis
Input Tables
OrderItems
| order_id(INTEGER) | product_id(INTEGER) |
|---|---|
| 1 | 101 |
| 1 | 102 |
| 2 | 101 |
| 2 | 102 |
| 2 | 103 |
| 3 | 101 |
| 3 | 102 |
| 4 | 104 |
| 4 | 105 |
Expected Output
| product_a(INTEGER) | product_b(INTEGER) | bundle_count(INTEGER) |
|---|---|---|
| 101 | 102 | 3 |
| 101 | 103 | 1 |
| 102 | 103 | 1 |
| 104 | 105 | 1 |
Tags
MediumASKED IN INTERVIEWSelf JoinAggregationFilteringMarket Basket Analysis
15-20 min
47%