Seller Second-Item Brand Affinity Audit

Hard

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 1

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 2

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 3

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.

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

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 |

[ {"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"} ]

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 |

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 |