Top Sellers by Distinct Cross-Brand Items

Medium

A marketplace analytics team is tracking catalog diversity among sellers. Table: users seller_id: unique seller identifier join_date: seller onboarding date favorite_brand: seller's preferred brand Table: items item_id: unique catalog item identifier item_brand: brand label of the item Table: orders order_id: unique order identifier order_date: order date item_id: sold item seller_id: seller who sold the item Cross-brand definition: A sale is cross-brand if item_brand != favorite_brand for that seller. Scoring rule: For each seller, count distinct item_id among only cross-brand sales. Task: Return the seller(s) with the maximum cross-brand distinct item count. Important behavior: Sellers with zero qualifying cross-brand items are excluded. If no seller has any qualifying cross-brand sale, return an empty result. If multiple sellers share the same maximum count, return all of them. Output columns (exact order): seller_id num_items Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

users:
| seller_id | join_date  | favorite_brand |
|-----------|------------|----------------|
| 1         | 2019-01-01 | Alpha          |
| 2         | 2019-02-09 | Beta           |
| 3         | 2019-01-19 | Gamma          |

items:
| item_id | item_brand |
|---------|------------|
| 1       | Beta       |
| 2       | Alpha      |
| 3       | Gamma      |
| 4       | Delta      |

orders:
| order_id | order_date  | item_id | seller_id |
|----------|-------------|---------|-----------|
| 1        | 2019-08-01  | 4       | 2         |
| 2        | 2019-08-02  | 2       | 3         |
| 3        | 2019-08-03  | 3       | 3         |
| 4        | 2019-08-04  | 1       | 2         |
| 5        | 2019-08-04  | 4       | 2         |

Output

[
  {"seller_id":2,"num_items":1},
  {"seller_id":3,"num_items":1}
]

Explanation

Seller 2 has one distinct cross-brand item (item 4). Seller 3 has one distinct cross-brand item (item 2). They tie for the maximum.

Example 2

Input

users:
| seller_id | join_date  | favorite_brand |
|-----------|------------|----------------|
| 10        | 2020-02-01 | Red            |
| 11        | 2020-02-10 | Blue           |

items:
| item_id | item_brand |
|---------|------------|
| 100     | Red        |
| 101     | Blue       |

orders:
| order_id | order_date  | item_id | seller_id |
|----------|-------------|---------|-----------|
| 9        | 2020-05-01  | 100     | 10        |
| 10       | 2020-05-02  | 101     | 11        |
| 11       | 2020-05-03  | 100     | 10        |

Output

[]

Explanation

Every sale matches each seller's favorite brand, so no seller has any qualifying cross-brand item.

Example 3

Input

users:
| seller_id | join_date  | favorite_brand |
|-----------|------------|----------------|
| 7         | 2021-03-01 | Nova           |
| 8         | 2021-03-02 | Terra          |
| 9         | 2021-03-03 | Orbit          |

items:
| item_id | item_brand |
|---------|------------|
| 100     | Nova       |
| 101     | Terra      |
| 102     | Orbit      |
| 103     | Flux       |
| 104     | Alloy      |

orders:
| order_id | order_date  | item_id | seller_id |
|----------|-------------|---------|-----------|
| 1        | 2021-05-01  | 103     | 7         |
| 2        | 2021-05-02  | 104     | 7         |
| 3        | 2021-05-03  | 103     | 7         |
| 4        | 2021-05-04  | 100     | 8         |
| 5        | 2021-05-05  | 102     | 8         |
| 6        | 2021-05-06  | 102     | 9         |

Output

[
  {"seller_id":7,"num_items":2},
  {"seller_id":8,"num_items":2}
]

Explanation

Seller 7 has cross-brand items {103, 104}. Seller 8 has cross-brand items {100, 102}. Seller 9 has no cross-brand items and is excluded.

Constraints

1 <= number of rows in users <= 400
1 <= number of rows in items <= 600
0 <= number of rows in orders <= 1200
users.seller_id is unique
items.item_id is unique
orders.order_id is unique
orders.item_id references items.item_id in valid datasets
orders.seller_id references users.seller_id in valid datasets
favorite_brand and item_brand are non-empty strings
Output columns must be exactly: seller_id, num_items
Include only sellers with num_items >= 1
Sort final output by seller_id ascending
If no qualifying seller exists, return an empty result
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: seller_id, num_items

Loading problem...

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

Top Sellers by Distinct Cross-Brand Items

Medium

A marketplace analytics team is tracking catalog diversity among sellers. Table: users seller_id: unique seller identifier join_date: seller onboarding date favorite_brand: seller's preferred brand Table: items item_id: unique catalog item identifier item_brand: brand label of the item Table: orders order_id: unique order identifier order_date: order date item_id: sold item seller_id: seller who sold the item Cross-brand definition: A sale is cross-brand if item_brand != favorite_brand for that seller. Scoring rule: For each seller, count distinct item_id among only cross-brand sales. Task: Return the seller(s) with the maximum cross-brand distinct item count. Important behavior: Sellers with zero qualifying cross-brand items are excluded. If no seller has any qualifying cross-brand sale, return an empty result. If multiple sellers share the same maximum count, return all of them. Output columns (exact order): seller_id num_items Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

users:
| seller_id | join_date  | favorite_brand |
|-----------|------------|----------------|
| 1         | 2019-01-01 | Alpha          |
| 2         | 2019-02-09 | Beta           |
| 3         | 2019-01-19 | Gamma          |

items:
| item_id | item_brand |
|---------|------------|
| 1       | Beta       |
| 2       | Alpha      |
| 3       | Gamma      |
| 4       | Delta      |

orders:
| order_id | order_date  | item_id | seller_id |
|----------|-------------|---------|-----------|
| 1        | 2019-08-01  | 4       | 2         |
| 2        | 2019-08-02  | 2       | 3         |
| 3        | 2019-08-03  | 3       | 3         |
| 4        | 2019-08-04  | 1       | 2         |
| 5        | 2019-08-04  | 4       | 2         |

Output

[
  {"seller_id":2,"num_items":1},
  {"seller_id":3,"num_items":1}
]

Explanation

Seller 2 has one distinct cross-brand item (item 4). Seller 3 has one distinct cross-brand item (item 2). They tie for the maximum.

Example 2

Input

users:
| seller_id | join_date  | favorite_brand |
|-----------|------------|----------------|
| 10        | 2020-02-01 | Red            |
| 11        | 2020-02-10 | Blue           |

items:
| item_id | item_brand |
|---------|------------|
| 100     | Red        |
| 101     | Blue       |

orders:
| order_id | order_date  | item_id | seller_id |
|----------|-------------|---------|-----------|
| 9        | 2020-05-01  | 100     | 10        |
| 10       | 2020-05-02  | 101     | 11        |
| 11       | 2020-05-03  | 100     | 10        |

Output

[]

Explanation

Every sale matches each seller's favorite brand, so no seller has any qualifying cross-brand item.

Example 3

Input

users:
| seller_id | join_date  | favorite_brand |
|-----------|------------|----------------|
| 7         | 2021-03-01 | Nova           |
| 8         | 2021-03-02 | Terra          |
| 9         | 2021-03-03 | Orbit          |

items:
| item_id | item_brand |
|---------|------------|
| 100     | Nova       |
| 101     | Terra      |
| 102     | Orbit      |
| 103     | Flux       |
| 104     | Alloy      |

orders:
| order_id | order_date  | item_id | seller_id |
|----------|-------------|---------|-----------|
| 1        | 2021-05-01  | 103     | 7         |
| 2        | 2021-05-02  | 104     | 7         |
| 3        | 2021-05-03  | 103     | 7         |
| 4        | 2021-05-04  | 100     | 8         |
| 5        | 2021-05-05  | 102     | 8         |
| 6        | 2021-05-06  | 102     | 9         |

Output

[
  {"seller_id":7,"num_items":2},
  {"seller_id":8,"num_items":2}
]

Explanation

Seller 7 has cross-brand items {103, 104}. Seller 8 has cross-brand items {100, 102}. Seller 9 has no cross-brand items and is excluded.

Constraints

1 <= number of rows in users <= 400
1 <= number of rows in items <= 600
0 <= number of rows in orders <= 1200
users.seller_id is unique
items.item_id is unique
orders.order_id is unique
orders.item_id references items.item_id in valid datasets
orders.seller_id references users.seller_id in valid datasets
favorite_brand and item_brand are non-empty strings
Output columns must be exactly: seller_id, num_items
Include only sellers with num_items >= 1
Sort final output by seller_id ascending
If no qualifying seller exists, return an empty result
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: seller_id, num_items