Cross-Sell Candidates: Bought A and B, Not C

Medium

A retail analytics platform tracks customer purchases by SKU and wants to run a focused recommendation campaign for product C. Table: customers customer_id: unique customer identifier customer_name: display name for the customer Table: orders order_id: unique purchase event identifier customer_id: customer who placed the order product_name: product code purchased in that row Goal: Find customers who are strong candidates for a product C recommendation. A customer is eligible if: they purchased product A at least once, they purchased product B at least once, they never purchased product C. Output requirements: Return exactly these columns, in this order: customer_id customer_name Sort result by customer_id ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

customers:
| customer_id | customer_name |
|-------------|---------------|
| 1           | Daniel        |
| 2           | Diana         |
| 3           | Elena         |
| 4           | John          |

orders:
| order_id | customer_id | product_name |
|----------|-------------|--------------|
| 10       | 1           | A            |
| 20       | 1           | B            |
| 30       | 1           | C            |
| 40       | 2           | A            |
| 50       | 3           | A            |
| 60       | 3           | B            |
| 70       | 3           | D            |
| 80       | 4           | C            |

Output

[
  {"customer_id":3,"customer_name":"Elena"}
]

Explanation

Customer 3 has both A and B and never bought C. Customer 1 is disqualified because C appears, and customers 2 and 4 are missing one of A/B.

Example 2

Input

customers:
| customer_id | customer_name |
|-------------|---------------|
| 11          | Priya         |
| 12          | Omar          |

orders:
| order_id | customer_id | product_name |
|----------|-------------|--------------|
| 100      | 11          | A            |
| 101      | 11          | C            |
| 102      | 12          | B            |

Output

[]

Explanation

No customer satisfies all eligibility rules at the same time.

Example 3

Input

customers:
| customer_id | customer_name |
|-------------|---------------|
| 21          | Mei           |
| 22          | Carlos        |
| 23          | Amina         |

orders:
| order_id | customer_id | product_name |
|----------|-------------|--------------|
| 201      | 21          | A            |
| 202      | 21          | B            |
| 203      | 21          | B            |
| 204      | 21          | E            |
| 205      | 22          | A            |
| 206      | 22          | B            |
| 207      | 22          | C            |
| 208      | 23          | A            |
| 209      | 23          | B            |
| 210      | 23          | F            |

Output

[
  {"customer_id":21,"customer_name":"Mei"},
  {"customer_id":23,"customer_name":"Amina"}
]

Explanation

Duplicates and unrelated products do not matter for eligibility. Customer 22 is excluded because product C appears.

Constraints

1 <= number of rows in customers <= 200000
0 <= number of rows in orders <= 300000
customers.customer_id is unique
orders.order_id is unique
orders.customer_id references customers.customer_id
product_name is compared case-sensitively for exact values 'A', 'B', and 'C'
orders may contain duplicate (customer_id, product_name) combinations, and duplicates are valid events
Return exactly two columns in this order: customer_id, customer_name
Sort by customer_id ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: customer_id, customer_name

Loading problem...

Preparing your editor. You can read the problem while it loads.

Cross-Sell Candidates: Bought A and B, Not C

Medium

A retail analytics platform tracks customer purchases by SKU and wants to run a focused recommendation campaign for product C. Table: customers customer_id: unique customer identifier customer_name: display name for the customer Table: orders order_id: unique purchase event identifier customer_id: customer who placed the order product_name: product code purchased in that row Goal: Find customers who are strong candidates for a product C recommendation. A customer is eligible if: they purchased product A at least once, they purchased product B at least once, they never purchased product C. Output requirements: Return exactly these columns, in this order: customer_id customer_name Sort result by customer_id ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

customers:
| customer_id | customer_name |
|-------------|---------------|
| 1           | Daniel        |
| 2           | Diana         |
| 3           | Elena         |
| 4           | John          |

orders:
| order_id | customer_id | product_name |
|----------|-------------|--------------|
| 10       | 1           | A            |
| 20       | 1           | B            |
| 30       | 1           | C            |
| 40       | 2           | A            |
| 50       | 3           | A            |
| 60       | 3           | B            |
| 70       | 3           | D            |
| 80       | 4           | C            |

Output

[
  {"customer_id":3,"customer_name":"Elena"}
]

Explanation

Customer 3 has both A and B and never bought C. Customer 1 is disqualified because C appears, and customers 2 and 4 are missing one of A/B.

Example 2

Input

customers:
| customer_id | customer_name |
|-------------|---------------|
| 11          | Priya         |
| 12          | Omar          |

orders:
| order_id | customer_id | product_name |
|----------|-------------|--------------|
| 100      | 11          | A            |
| 101      | 11          | C            |
| 102      | 12          | B            |

Output

[]

Explanation

No customer satisfies all eligibility rules at the same time.

Example 3

Input

customers:
| customer_id | customer_name |
|-------------|---------------|
| 21          | Mei           |
| 22          | Carlos        |
| 23          | Amina         |

orders:
| order_id | customer_id | product_name |
|----------|-------------|--------------|
| 201      | 21          | A            |
| 202      | 21          | B            |
| 203      | 21          | B            |
| 204      | 21          | E            |
| 205      | 22          | A            |
| 206      | 22          | B            |
| 207      | 22          | C            |
| 208      | 23          | A            |
| 209      | 23          | B            |
| 210      | 23          | F            |

Output

[
  {"customer_id":21,"customer_name":"Mei"},
  {"customer_id":23,"customer_name":"Amina"}
]

Explanation

Duplicates and unrelated products do not matter for eligibility. Customer 22 is excluded because product C appears.

Constraints

1 <= number of rows in customers <= 200000
0 <= number of rows in orders <= 300000
customers.customer_id is unique
orders.order_id is unique
orders.customer_id references customers.customer_id
product_name is compared case-sensitively for exact values 'A', 'B', and 'C'
orders may contain duplicate (customer_id, product_name) combinations, and duplicates are valid events
Return exactly two columns in this order: customer_id, customer_name
Sort by customer_id ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: customer_id, customer_name