00:00:00

Description

Editorial

Seller Second-Item Brand Affinity Audit

HARD30 pts

You are given three relational tables from a commerce platform:

users: user profile and favorite brand preferences
- user_id
- join_date
- favorite_brand
orders: completed transactions
- order_id
- order_date
- item_id
- buyer_id
- seller_id
items: product catalog metadata
- item_id
- item_brand

For each user, determine whether the brand of the second item they sold matches their favorite_brand.

Rules and definitions:

A user's sales must be ordered by order_date ascending.
If deterministic tie-breaking is needed, use order_id ascending.
If a user sold fewer than two items, report "no" for that user.
Output one row for every user.

Return exactly these columns in this order:

seller_id
second_item_favorite

where second_item_favorite is either "yes" or "no".

This problem must be solvable in both:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

users:
| user_id | join_date  | favorite_brand |
|---------|------------|----------------|
| 1       | 2019-01-01 | Nova           |
| 2       | 2019-02-09 | Echo           |
| 3       | 2019-01-19 | Luma           |
| 4       | 2019-05-21 | Pulse          |

orders:
| order_id | order_date  | item_id | buyer_id | seller_id |
|----------|-------------|---------|----------|-----------|
| 1        | 2019-08-01  | 10      | 1        | 2         |
| 2        | 2019-08-02  | 20      | 1        | 3         |
| 3        | 2019-08-03  | 30      | 2        | 3         |
| 4        | 2019-08-04  | 40      | 4        | 2         |
| 5        | 2019-08-04  | 40      | 3        | 4         |
| 6        | 2019-08-05  | 20      | 2        | 4         |

items:
| item_id | item_brand |
|---------|------------|
| 10      | Pulse      |
| 20      | Nova       |
| 30      | Luma       |
| 40      | Echo       |

Output

[
  {"seller_id": 1, "second_item_favorite": "no"},
  {"seller_id": 2, "second_item_favorite": "yes"},
  {"seller_id": 3, "second_item_favorite": "yes"},
  {"seller_id": 4, "second_item_favorite": "no"}
]

Explanation

User 1 never sold an item, so the answer is no. User 2's second sold item has brand Echo, matching favorite_brand Echo. User 3's second sold item has brand Luma, matching favorite_brand Luma. User 4's second sold item is brand Nova, which does not match favorite_brand Pulse.

Example

Input

users:
| user_id | join_date  | favorite_brand |
|---------|------------|----------------|
| 10      | 2019-03-01 | Atlas          |
| 11      | 2019-03-02 | Sol            |

orders:
| order_id | order_date  | item_id | buyer_id | seller_id |
|----------|-------------|---------|----------|-----------|
| 100      | 2019-07-10  | 501     | 11       | 10        |
| 101      | 2019-07-11  | 502     | 10       | 10        |
| 102      | 2019-07-12  | 503     | 10       | 11        |

items:
| item_id | item_brand |
|---------|------------|
| 501     | Atlas      |
| 502     | Atlas      |
| 503     | Sol        |

Output

[
  {"seller_id": 10, "second_item_favorite": "yes"},
  {"seller_id": 11, "second_item_favorite": "no"}
]

Explanation

User 10 has two sales and the second sold item is brand Atlas, which matches favorite_brand Atlas. User 11 has only one sale, so the answer is no.

Example

Input

users:
| user_id | join_date  | favorite_brand |
|---------|------------|----------------|
| 21      | 2020-01-01 | Rhea           |
| 22      | 2020-01-02 | Orion          |

orders:
| order_id | order_date  | item_id | buyer_id | seller_id |
|----------|-------------|---------|----------|-----------|
| 901      | 2020-06-03  | 77      | 21       | 22        |
| 900      | 2020-06-01  | 78      | 22       | 22        |
| 902      | 2020-06-08  | 79      | 21       | 22        |

items:
| item_id | item_brand |
|---------|------------|
| 77      | Orion      |
| 78      | Rhea       |
| 79      | Nova       |

Output

[
  {"seller_id": 21, "second_item_favorite": "no"},
  {"seller_id": 22, "second_item_favorite": "yes"}
]

Explanation

For seller 22, chronological order is by order_date (2020-06-01, 2020-06-03, 2020-06-08). The second sale is item 77 (brand Orion), which matches favorite_brand Orion. User 21 has no sales and therefore outputs no.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in users <= 5,000
1 <= number of rows in items <= 5,000
0 <= number of rows in orders <= 20,000
users.user_id is unique
items.item_id is unique
orders.order_id is unique
orders.item_id references items.item_id in valid datasets
orders.buyer_id and orders.seller_id reference users.user_id in valid datasets
No seller has more than one sold item on the same order_date
Output must include exactly one row per user
Output columns must be exactly: seller_id, second_item_favorite
second_item_favorite must be either "yes" or "no"
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: seller_id, second_item_favorite

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

items =

[{"item_id":10,"item_brand":"Pulse"},{"item_id":20,"item_brand":"Nova"},{"item_id":30,"item_brand":"Luma"},{"item_id":40,"item_brand":"Echo"}]

users =

[{"user_id":1,"join_date":"2019-01-01","favorite_brand":"Nova"},{"user_id":2,"join_date":"2019-02-09","favorite_brand":"Echo"},{"user_id":3,"join_date":"2019-01-19","favorite_brand":"Luma"},{"user_id":4,"join_date":"2019-05-21","favorite_brand":"Pulse"}]

orders =

[{"order_id":1,"order_date":"2019-08-01","item_id":10,"buyer_id":1,"seller_id":2},{"order_id":2,"order_date":"2019-08-02","item_id":20,"buyer_id":1,"seller_id":3},{"order_id":3,"order_date":"2019-08-03","item_id":30,"buyer_id":2,"seller_id":3},{"order_id":4,"order_date":"2019-08-04","item_id":40,"buyer_id":4,"seller_id":2},{"order_id":5,"order_date":"2019-08-04","item_id":40,"buyer_id":3,"seller_id":4},{"order_id":6,"order_date":"2019-08-05","item_id":20,"buyer_id":2,"seller_id":4}]

Loading problem...

101

00:00:00

Description

Editorial

Seller Second-Item Brand Affinity Audit

HARD30 pts

You are given three relational tables from a commerce platform:

users: user profile and favorite brand preferences
- user_id
- join_date
- favorite_brand
orders: completed transactions
- order_id
- order_date
- item_id
- buyer_id
- seller_id
items: product catalog metadata
- item_id
- item_brand

For each user, determine whether the brand of the second item they sold matches their favorite_brand.

Rules and definitions:

A user's sales must be ordered by order_date ascending.
If deterministic tie-breaking is needed, use order_id ascending.
If a user sold fewer than two items, report "no" for that user.
Output one row for every user.

Return exactly these columns in this order:

seller_id
second_item_favorite

where second_item_favorite is either "yes" or "no".

This problem must be solvable in both:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

users:
| user_id | join_date  | favorite_brand |
|---------|------------|----------------|
| 1       | 2019-01-01 | Nova           |
| 2       | 2019-02-09 | Echo           |
| 3       | 2019-01-19 | Luma           |
| 4       | 2019-05-21 | Pulse          |

orders:
| order_id | order_date  | item_id | buyer_id | seller_id |
|----------|-------------|---------|----------|-----------|
| 1        | 2019-08-01  | 10      | 1        | 2         |
| 2        | 2019-08-02  | 20      | 1        | 3         |
| 3        | 2019-08-03  | 30      | 2        | 3         |
| 4        | 2019-08-04  | 40      | 4        | 2         |
| 5        | 2019-08-04  | 40      | 3        | 4         |
| 6        | 2019-08-05  | 20      | 2        | 4         |

items:
| item_id | item_brand |
|---------|------------|
| 10      | Pulse      |
| 20      | Nova       |
| 30      | Luma       |
| 40      | Echo       |

Output

[
  {"seller_id": 1, "second_item_favorite": "no"},
  {"seller_id": 2, "second_item_favorite": "yes"},
  {"seller_id": 3, "second_item_favorite": "yes"},
  {"seller_id": 4, "second_item_favorite": "no"}
]

Explanation

Example

Input

users:
| user_id | join_date  | favorite_brand |
|---------|------------|----------------|
| 10      | 2019-03-01 | Atlas          |
| 11      | 2019-03-02 | Sol            |

orders:
| order_id | order_date  | item_id | buyer_id | seller_id |
|----------|-------------|---------|----------|-----------|
| 100      | 2019-07-10  | 501     | 11       | 10        |
| 101      | 2019-07-11  | 502     | 10       | 10        |
| 102      | 2019-07-12  | 503     | 10       | 11        |

items:
| item_id | item_brand |
|---------|------------|
| 501     | Atlas      |
| 502     | Atlas      |
| 503     | Sol        |

Output

[
  {"seller_id": 10, "second_item_favorite": "yes"},
  {"seller_id": 11, "second_item_favorite": "no"}
]

Explanation

User 10 has two sales and the second sold item is brand Atlas, which matches favorite_brand Atlas. User 11 has only one sale, so the answer is no.

Example

Input

users:
| user_id | join_date  | favorite_brand |
|---------|------------|----------------|
| 21      | 2020-01-01 | Rhea           |
| 22      | 2020-01-02 | Orion          |

orders:
| order_id | order_date  | item_id | buyer_id | seller_id |
|----------|-------------|---------|----------|-----------|
| 901      | 2020-06-03  | 77      | 21       | 22        |
| 900      | 2020-06-01  | 78      | 22       | 22        |
| 902      | 2020-06-08  | 79      | 21       | 22        |

items:
| item_id | item_brand |
|---------|------------|
| 77      | Orion      |
| 78      | Rhea       |
| 79      | Nova       |

Output

[
  {"seller_id": 21, "second_item_favorite": "no"},
  {"seller_id": 22, "second_item_favorite": "yes"}
]

Explanation

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in users <= 5,000
1 <= number of rows in items <= 5,000
0 <= number of rows in orders <= 20,000
users.user_id is unique
items.item_id is unique
orders.order_id is unique
orders.item_id references items.item_id in valid datasets
orders.buyer_id and orders.seller_id reference users.user_id in valid datasets
No seller has more than one sold item on the same order_date
Output must include exactly one row per user
Output columns must be exactly: seller_id, second_item_favorite
second_item_favorite must be either "yes" or "no"
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: seller_id, second_item_favorite

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

items =

[{"item_id":10,"item_brand":"Pulse"},{"item_id":20,"item_brand":"Nova"},{"item_id":30,"item_brand":"Luma"},{"item_id":40,"item_brand":"Echo"}]

users =

orders =

Seller Second-Item Brand Affinity Audit

Hints

Seller Second-Item Brand Affinity Audit

Hints