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

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 |

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 |

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 |