00:00:00

Description

Editorial

Frequent Co-Purchase Product Pair Recommendations

MEDIUM20 pts

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:
1. customer_count descending
2. product1_id ascending
3. product2_id ascending

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

ProductInfo =

[{"product_id":101,"category":"Electronics","price":120},{"product_id":102,"category":"Books","price":25},{"product_id":103,"category":"Fitness","price":70},{"product_id":104,"category":"Home","price":45}]

ProductPurchases =

[{"user_id":1,"product_id":101,"quantity":2},{"user_id":1,"product_id":102,"quantity":1},{"user_id":1,"product_id":103,"quantity":1},{"user_id":2,"product_id":101,"quantity":1},{"user_id":2,"product_id":102,"quantity":4},{"user_id":2,"product_id":104,"quantity":1},{"user_id":3,"product_id":101,"quantity":1},{"user_id":3,"product_id":102,"quantity":2},{"user_id":3,"product_id":103,"quantity":2},{"user_id":4,"product_id":101,"quantity":1},{"user_id":4,"product_id":103,"quantity":3},{"user_id":4,"product_id":104,"quantity":1},{"user_id":5,"product_id":102,"quantity":2},{"user_id":5,"product_id":104,"quantity":1}]

Loading problem...

101

00:00:00

Description

Editorial

Frequent Co-Purchase Product Pair Recommendations

MEDIUM20 pts

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:
1. customer_count descending
2. product1_id ascending
3. product2_id ascending

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

ProductInfo =

ProductPurchases =

Frequent Co-Purchase Product Pair Recommendations

Hints

Frequent Co-Purchase Product Pair Recommendations

Hints