Market Analysis III - Practice Coding Problems

Market Analysis III - Problem

Database Medium

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

Understanding the Visualization

Join

Combine Users, Orders, Items tables

Filter

Keep only non-favorite brand sales

Aggregate

Count unique items per seller

Find Max

Identify sellers with highest counts

Key Takeaway

🎯 Key Insight: Use CTE to separate complex logic into clear steps: join, filter, aggregate, then find maximum

Asked in

A Amazon 12 M Microsoft 8 F Facebook 6

Use CTE with JOINs to combine all three tables, filter for non-favorite brands, count distinct items per seller, then find sellers with the maximum count using a CROSS JOIN with the maximum value.

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

Common Approaches

Approach	Time	Space	Notes
✓ CTE with Window Function	O(n + m + k)	O(n)	Use CTE to calculate counts, then window function to find maximum

CTE with Window Function — Algorithm Steps

Step 1: Join Users, Orders, and Items tables
Step 2: Filter where item_brand ≠ favorite_brand
Step 3: Count distinct items per seller using CTE
Step 4: Use window function to find maximum count
Step 5: Filter for sellers with maximum count

Visualization

Tap to expand

Step-by-Step Walkthrough

Join Tables

Combine Users, Orders, Items

Filter & Count

Find non-favorite brands, count unique items

Find Maximum

Identify sellers with highest count

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n + m + k)

Where n, m, k are sizes of Users, Orders, Items tables

✓ Linear Growth

Space Complexity

O(n)

CTE storage for seller counts

⚡ Linearithmic Space

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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

CTE with Window Function — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler