Market Basket Item-Chain Frequency

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

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

Database Schema (Inferred)

Orders

Column Name	Example Value
order_id	1
customer_id	101
order_date	2024-01-01

OrderItems

Column Name	Example Value
order_id	1
item_id	1
item_name	Apple

Example

Orders

order_id	customer_id	order_date
1	101	2024-01-01
2	102	2024-01-02
3	103	2024-01-03
4	101	2024-01-04

OrderItems

order_id	item_id	item_name
1	1	Apple
1	2	Banana
1	3	Cherry
2	1	Apple
2	3	Cherry
3	2	Banana
3	3	Cherry
3	4	Date
4	1	Apple
4	2	Banana
4	4	Date

Output

chain	chain_length	order_count
Apple -> Banana	2	2
Apple -> Cherry	2	2
Banana -> Cherry	2	2
Banana -> Date	2	2
Apple -> Banana -> Cherry	3	1
Apple -> Banana -> Date	3	1
Banana -> Cherry -> Date	3	1
Apple -> Date	2	1
Cherry -> Date	2	1

Explanation:

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.