Market Basket Item-Chain Frequency
Preview mode. Log in to edit, run, submit, and save progress.
Market Basket Item-Chain Frequency
You are given Orders and OrderItems tables. Using a recursive CTE, generate all item chains (sequences of 2 or 3 items sorted by item_id) that appear together in the same order. Count how many distinct orders contain each chain. Return (chain, chain_length, order_count) ordered by order_count DESC, then chain_length DESC, then chain ASC. Only return chains of length 2 or 3. A chain is a string of item names separated by ' -> ', where items are listed in ascending item_id order. Table: Orders
| Column Name | Type | Description |
|---|---|---|
| order_id | INT | Primary key |
| customer_id | INT | Customer identifier |
| order_date | DATE | Date of order |
Table: OrderItems
| Column Name | Type | Description |
|---|---|---|
| order_id | INT | References Orders |
| item_id | INT | Item identifier (used for ordering) |
| item_name | VARCHAR | Human-readable item name |
Examples
Anchor: each item in an order as a chain of length 1. Recursive step: extend chains by joining any item from the same order with a higher item_id, guarding against revisits via chain_ids string. Filter for chain_length > 1 in final aggregation. Count DISTINCT order_id per chain.
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.