Loading content...

00:00:00

Description

Editorial

Top Sellers by Distinct Cross-Brand Items

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

items =

[{"item_id":1,"item_brand":"Beta"},{"item_id":2,"item_brand":"Alpha"},{"item_id":3,"item_brand":"Gamma"},{"item_id":4,"item_brand":"Delta"}]

users =

[{"seller_id":1,"join_date":"2019-01-01","favorite_brand":"Alpha"},{"seller_id":2,"join_date":"2019-02-09","favorite_brand":"Beta"},{"seller_id":3,"join_date":"2019-01-19","favorite_brand":"Gamma"}]

orders =

[{"order_id":1,"order_date":"2019-08-01","item_id":4,"seller_id":2},{"order_id":2,"order_date":"2019-08-02","item_id":2,"seller_id":3},{"order_id":3,"order_date":"2019-08-03","item_id":3,"seller_id":3},{"order_id":4,"order_date":"2019-08-04","item_id":1,"seller_id":2},{"order_id":5,"order_date":"2019-08-04","item_id":4,"seller_id":2}]

Top Sellers by Distinct Cross-Brand Items

Hints

Top Sellers by Distinct Cross-Brand Items

Hints