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
Find Category Recommendation Pairs INPUT order_id cust_id category 1 101 Electronics 2 101 Books 3 102 Electronics 4 102 Books 5 103 Electronics 6 103 Books 7 104 Clothing 8 101 Clothing Customer-Category Map Cust 101 Cust 102 Cust 103 Elec, Books, Clothing Elec, Books Elec, Books ALGORITHM STEPS 1 Self-Join Orders Join table on customer_id where cat1 < cat2 2 Generate Category Pairs Create (cat1, cat2) pairs for each customer 3 Count Unique Customers GROUP BY category pairs COUNT(DISTINCT cust_id) 4 Filter Threshold HAVING count >= 3 Keep valid pairs only SELECT o1.category, o2.category, COUNT(DISTINCT cust_id) FROM orders o1 JOIN o2 WHERE o1.cat < o2.cat HAVING COUNT(*) >= 3 FINAL RESULT Category Recommendation Pairs category1 category2 cnt Books Electronics 3 Customers who bought BOTH: Books Electronics 3 Unique Customers: 101 102 103 OK - Valid Pair Found! count(3) >= threshold(3) Key Insight: Self-join the orders table to find customers who purchased from multiple categories. Using category1 < category2 ensures unique pairs and avoids duplicates like (A,B) and (B,A). HAVING clause filters pairs with at least 3 common customers for meaningful recommendations. TutorialsPoint - Find Category Recommendation Pairs | Optimal Solution

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