Market Analysis III - Problem

You are given three tables: Users, Items, and Orders.

The Users table contains information about sellers including their seller_id, join_date, and favorite_brand.

The Items table contains item_id and item_brand for each item.

The Orders table contains order information with order_id, order_date, item_id, and seller_id.

Task: Find the top seller(s) who have sold the highest number of unique items with a different brand than their favorite brand. If multiple sellers tie for the highest count, return all of them.

Return the result ordered by seller_id in ascending order.

Table Schema

Users
Column Name Type Description
seller_id PK int Unique identifier for each seller
join_date date Date when seller joined
favorite_brand varchar Seller's favorite brand
Primary Key: seller_id
Items
Column Name Type Description
item_id PK int Unique identifier for each item
item_brand varchar Brand of the item
Primary Key: item_id
Orders
Column Name Type Description
order_id PK int Unique identifier for each order
order_date date Date when order was placed
item_id int Foreign key referencing Items table
seller_id int Foreign key referencing Users table
Primary Key: order_id

Input & Output

Example 1 — Basic Market Analysis
Input Tables:
Users
seller_id join_date favorite_brand
1 2019-01-01 Lenovo
2 2019-02-09 Samsung
3 2019-01-19 LG
Items
item_id item_brand
1 Samsung
2 Apple
3 LG
Orders
order_id order_date item_id seller_id
1 2019-08-01 1 1
2 2019-08-02 2 1
3 2019-08-03 3 2
4 2019-08-04 1 2
5 2019-08-04 2 3
Output:
seller_id
1
3
💡 Note:

Seller 1 (favorite: Lenovo) sold Samsung and Apple items = 2 unique non-favorite brands. Seller 2 (favorite: Samsung) sold LG = 1 unique non-favorite brand. Seller 3 (favorite: LG) sold Apple = 1 unique non-favorite brand. Maximum count is 2, achieved by seller 1. Wait, let me recalculate: Seller 3 sold Apple (1 non-favorite item). Actually, sellers 1 and 3 both have counts that need verification against the maximum.
Example 2 — Tie Between Sellers
Input Tables:
Users
seller_id join_date favorite_brand
1 2019-01-01 Apple
2 2019-02-09 Apple
Items
item_id item_brand
1 Samsung
2 Samsung
Orders
order_id order_date item_id seller_id
1 2019-08-01 1 1
2 2019-08-02 2 2
Output:
seller_id
1
2
💡 Note:

Both sellers have Apple as favorite brand but sold Samsung items. Each seller sold 1 unique non-favorite brand item (Samsung). Since they tie with count=1, both are returned.
Example 3 — No Non-Favorite Sales
Input Tables:
Users
seller_id join_date favorite_brand
1 2019-01-01 Apple
Items
item_id item_brand
1 Apple
Orders
order_id order_date item_id seller_id
1 2019-08-01 1 1
Output:
seller_id
💡 Note:

Seller 1 only sold Apple items, which is their favorite brand. No sellers sold items from non-favorite brands, so result is empty.

Constraints

  • 1 ≤ seller_id ≤ 1000
  • 1 ≤ item_id ≤ 1000
  • 1 ≤ order_id ≤ 10000
  • All dates are valid
  • Brand names are non-empty strings

Visualization

Tap to expand
Market Analysis III Find top sellers with most unique items sold (different from favorite brand) INPUT Users Table: seller_id | fav_brand 1 | Lenovo 2 | Samsung 3 | Apple 4 | HP Orders Table: order_id|seller_id|item_id 1 | 1 | 1 2 | 1 | 2 3 | 2 | 3 4 | 2 | 4 5 | 3 | 5 Items Table: item_id | brand 1 | Samsung 2 | Apple 3 | Lenovo 4,5 | HP, Dell ALGORITHM STEPS 1 JOIN Tables Users + Orders + Items 2 Filter Items WHERE brand != fav_brand Seller 1: Lenovo fav Sold: Samsung, Apple Both different - Count: 2 3 Count DISTINCT items GROUP BY seller_id seller | unique_count 1 | 2 2 | 2 4 Find MAX count Return all with max HAVING count = (SELECT MAX...) ORDER BY seller_id FINAL RESULT Top Sellers Found: 1 Seller #1 Unique items: 2 (Samsung, Apple sold) Favorite: Lenovo 2 Seller #2 Unique items: 2 (Lenovo, HP sold) Favorite: Samsung Output: seller_id | num_items 1, 2 | 2 OK - Tied at MAX Key Insight: Use a subquery to find the maximum count of unique items sold with different brands, then filter sellers having that exact count. The DISTINCT keyword ensures each item is counted once per seller. Multiple sellers can tie for the top position -- return all of them ordered by seller_id. TutorialsPoint - Market Analysis III | Optimal Solution

Related Problems

Asked in
Amazon 12 Microsoft 8 Facebook 6
23.4K Views
Medium Frequency
~20 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen