00:00:00

Description

Editorial

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

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

orders =

[{"order_id":10,"customer_id":1,"product_name":"A"},{"order_id":20,"customer_id":1,"product_name":"B"},{"order_id":30,"customer_id":1,"product_name":"C"},{"order_id":40,"customer_id":2,"product_name":"A"},{"order_id":50,"customer_id":3,"product_name":"A"},{"order_id":60,"customer_id":3,"product_name":"B"},{"order_id":70,"customer_id":3,"product_name":"D"},{"order_id":80,"customer_id":4,"product_name":"C"}]

customers =

[{"customer_id":1,"customer_name":"Daniel"},{"customer_id":2,"customer_name":"Diana"},{"customer_id":3,"customer_name":"Elena"},{"customer_id":4,"customer_name":"John"}]

Loading problem...

101

00:00:00

Description

Editorial

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

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

orders =

customers =

[{"customer_id":1,"customer_name":"Daniel"},{"customer_id":2,"customer_name":"Diana"},{"customer_id":3,"customer_name":"Elena"},{"customer_id":4,"customer_name":"John"}]

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

Hints

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

Hints