Frequent Co-Purchase Product Pair Recommendations

Medium

A retail intelligence team is building a "buy together" recommendation widget for product detail pages. Table: ProductPurchases user_id: customer identifier product_id: purchased product identifier quantity: units purchased in that transaction record Table: ProductInfo product_id: unique product identifier category: merchandising category name price: catalog price for the product Task: Find product pairs that are commonly purchased by the same customers. Rules: Build unordered product pairs per user using product1_id < product2_id. A pair is considered recommendation-worthy only if at least 3 distinct users purchased both products. For every qualifying pair, attach category labels for both products from ProductInfo. Output requirements: Return columns in this exact order: product1_id product2_id product1_category product2_category customer_count Sort by: customer_count descending product1_id ascending product2_id ascending Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

ProductPurchases:
| user_id | product_id | quantity |
|---------|------------|----------|
| 1       | 101        | 2        |
| 1       | 102        | 1        |
| 1       | 103        | 1        |
| 2       | 101        | 1        |
| 2       | 102        | 4        |
| 2       | 104        | 1        |
| 3       | 101        | 1        |
| 3       | 102        | 2        |
| 3       | 103        | 2        |
| 4       | 101        | 1        |
| 4       | 103        | 3        |
| 4       | 104        | 1        |
| 5       | 102        | 2        |
| 5       | 104        | 1        |

ProductInfo:
| product_id | category     | price |
|------------|--------------|-------|
| 101        | Electronics  | 120   |
| 102        | Books        | 25    |
| 103        | Fitness      | 70    |
| 104        | Home         | 45    |

Output

[
  {"product1_id":101,"product2_id":102,"product1_category":"Electronics","product2_category":"Books","customer_count":3},
  {"product1_id":101,"product2_id":103,"product1_category":"Electronics","product2_category":"Fitness","customer_count":3}
]

Explanation

Pair (101,102) appears for users 1, 2, and 3. Pair (101,103) appears for users 1, 3, and 4. Other pairs do not reach the minimum threshold of 3 distinct users.

Example 2

Input

ProductPurchases:
| user_id | product_id | quantity |
|---------|------------|----------|
| 10      | 201        | 1        |
| 10      | 202        | 1        |
| 11      | 201        | 2        |
| 11      | 202        | 1        |
| 11      | 203        | 2        |
| 12      | 201        | 1        |
| 12      | 203        | 1        |
| 13      | 202        | 1        |
| 13      | 203        | 3        |
| 14      | 201        | 1        |
| 14      | 202        | 1        |
| 14      | 203        | 1        |

ProductInfo:
| product_id | category     | price |
|------------|--------------|-------|
| 201        | Kitchen      | 40    |
| 202        | Garden       | 55    |
| 203        | Storage      | 30    |

Output

[
  {"product1_id":201,"product2_id":202,"product1_category":"Kitchen","product2_category":"Garden","customer_count":3},
  {"product1_id":201,"product2_id":203,"product1_category":"Kitchen","product2_category":"Storage","customer_count":3},
  {"product1_id":202,"product2_id":203,"product1_category":"Garden","product2_category":"Storage","customer_count":3}
]

Explanation

All three pairs have equal customer_count = 3, so tie-breaking falls back to product1_id and then product2_id ascending.

Example 3

Input

ProductPurchases:
| user_id | product_id | quantity |
|---------|------------|----------|
| 21      | 301        | 1        |
| 21      | 302        | 1        |
| 22      | 301        | 1        |
| 22      | 303        | 1        |
| 23      | 302        | 1        |
| 23      | 303        | 1        |

ProductInfo:
| product_id | category     | price |
|------------|--------------|-------|
| 301        | Office       | 15    |
| 302        | Stationery   | 5     |
| 303        | Accessories  | 12    |

Output

[]

Explanation

Every pair is shared by fewer than 3 users, so no recommendation pair qualifies for output.

Constraints

1 <= number of rows in ProductPurchases <= 300000
2 <= number of rows in ProductInfo <= 100000
(user_id, product_id) is unique in ProductPurchases
product_id is unique in ProductInfo
1 <= user_id <= 1000000000
1 <= product_id <= 1000000000
1 <= quantity <= 100000
category is a non-empty string
0 < price <= 1000000
Every ProductPurchases.product_id exists in ProductInfo.product_id
A qualifying pair must have customer_count >= 3
Each user contributes at most once to a given product pair
Output columns must be exactly: product1_id, product2_id, product1_category, product2_category, customer_count
Final order must be customer_count DESC, product1_id ASC, product2_id ASC
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with required columns

ProductPurchases: | user_id | product_id | quantity | |---------|------------|----------| | 1 | 101 | 2 | | 1 | 102 | 1 | | 1 | 103 | 1 | | 2 | 101 | 1 | | 2 | 102 | 4 | | 2 | 104 | 1 | | 3 | 101 | 1 | | 3 | 102 | 2 | | 3 | 103 | 2 | | 4 | 101 | 1 | | 4 | 103 | 3 | | 4 | 104 | 1 | | 5 | 102 | 2 | | 5 | 104 | 1 | ProductInfo: | product_id | category | price | |------------|--------------|-------| | 101 | Electronics | 120 | | 102 | Books | 25 | | 103 | Fitness | 70 | | 104 | Home | 45 |

[ {"product1_id":101,"product2_id":102,"product1_category":"Electronics","product2_category":"Books","customer_count":3}, {"product1_id":101,"product2_id":103,"product1_category":"Electronics","product2_category":"Fitness","customer_count":3} ]

ProductPurchases: | user_id | product_id | quantity | |---------|------------|----------| | 10 | 201 | 1 | | 10 | 202 | 1 | | 11 | 201 | 2 | | 11 | 202 | 1 | | 11 | 203 | 2 | | 12 | 201 | 1 | | 12 | 203 | 1 | | 13 | 202 | 1 | | 13 | 203 | 3 | | 14 | 201 | 1 | | 14 | 202 | 1 | | 14 | 203 | 1 | ProductInfo: | product_id | category | price | |------------|--------------|-------| | 201 | Kitchen | 40 | | 202 | Garden | 55 | | 203 | Storage | 30 |

[ {"product1_id":201,"product2_id":202,"product1_category":"Kitchen","product2_category":"Garden","customer_count":3}, {"product1_id":201,"product2_id":203,"product1_category":"Kitchen","product2_category":"Storage","customer_count":3}, {"product1_id":202,"product2_id":203,"product1_category":"Garden","product2_category":"Storage","customer_count":3} ]

ProductPurchases: | user_id | product_id | quantity | |---------|------------|----------| | 21 | 301 | 1 | | 21 | 302 | 1 | | 22 | 301 | 1 | | 22 | 303 | 1 | | 23 | 302 | 1 | | 23 | 303 | 1 | ProductInfo: | product_id | category | price | |------------|--------------|-------| | 301 | Office | 15 | | 302 | Stationery | 5 | | 303 | Accessories | 12 |