Find Category Recommendation Pairs - Problem

Amazon wants to understand shopping patterns across product categories. You have two tables:

ProductPurchases table contains user purchase data with columns user_id, product_id, and quantity.

ProductInfo table contains product details with columns product_id, category, and price.

Write a solution to:

  • Find all category pairs where category1 < category2 alphabetically
  • For each category pair, count the number of unique customers who purchased products from both categories
  • Only include category pairs where at least 3 different customers have purchased from both categories

Return results ordered by customer_count descending, then by category1 ascending, then by category2 ascending.

Table Schema

ProductPurchases
Column Name Type Description
user_id PK int User identifier
product_id PK int Product identifier
quantity int Quantity purchased
Primary Key: (user_id, product_id)
Note: Each row represents a purchase of a product by a user
ProductInfo
Column Name Type Description
product_id PK int Product identifier
category varchar Product category
price decimal Product price
Primary Key: product_id
Note: Each row assigns a category and price to a product

Input & Output

Example 1 — Cross-Category Shopping Pattern
Input Tables:
ProductPurchases
user_id product_id quantity
1 101 2
1 201 1
2 101 1
2 301 3
3 201 1
3 301 2
4 101 1
4 201 1
ProductInfo
product_id category price
101 Electronics 299.99
201 Books 19.99
301 Clothing 49.99
Output:
category1 category2 customer_count
💡 Note:

Users 1, 2, 3, and 4 make purchases across different categories. User 1 bought Electronics and Books, User 2 bought Electronics and Clothing, User 3 bought Books and Clothing, User 4 bought Electronics and Books. However, no category pair has at least 3 customers, so no pairs are reportable.
Example 2 — Reportable Category Pairs
Input Tables:
ProductPurchases
user_id product_id quantity
1 101 1
1 201 1
2 101 2
2 201 1
3 101 1
3 201 3
4 101 1
4 201 1
ProductInfo
product_id category price
101 Electronics 299.99
201 Books 19.99
Output:
category1 category2 customer_count
Books Electronics 4
💡 Note:

All 4 users (1, 2, 3, 4) purchased products from both Books and Electronics categories. Since 4 ≥ 3, this category pair is reportable. The pair is ordered as 'Books', 'Electronics' since 'Books' < 'Electronics' alphabetically.

Constraints

  • 1 ≤ user_id ≤ 10000
  • 1 ≤ product_id ≤ 10000
  • 1 ≤ quantity ≤ 100
  • category is a non-empty string
  • price is a positive decimal value

Visualization

Tap to expand
Category Recommendation AnalysisProductPurchasesuser_idproduct_idquantity1101112011ProductInfoproduct_idcategoryprice101Electronics299.99201Books19.99JOIN + SELF-JOINFind cross-categoryCategory Pairscategory1category2customersBooksElectronics4Users 1,2,3,4 all bought from both Books and Electronics (≥3 customers)
Understanding the Visualization
1
Join Data
Connect purchases with product categories
2
Self-Join
Find users with multiple categories
3
Filter & Count
Group by category pairs and count customers
Key Takeaway
🎯 Key Insight: Use self-join to efficiently find customers with purchases across multiple categories

Related Problems

Asked in
Amazon 28 Google 15 Meta 12
23.4K Views
High Frequency
~18 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen