Market Analysis II - Practice Coding Problems

Market Analysis II - Problem

Database Hard

You have three tables in an online marketplace database:

Users table: Contains user information including their user_id, join_date, and favorite_brand.

Orders table: Contains order information with order_id, order_date, item_id, buyer_id, and seller_id.

Items table: Contains item information with item_id and item_brand.

Task: For each user, determine whether the brand of the second item (chronologically by date) they sold matches their favorite brand. If a user sold fewer than two items, report 'no' for that user.

Note: It is guaranteed that no seller sells more than one item per day, so there are no tie-breaking issues for ordering by date.

Table Schema

Users

Column Name	Type	Description
`user_id` PK	int	Primary key, unique user identifier
`join_date`	date	Date when user joined the platform
`favorite_brand`	varchar	User's favorite brand preference

Primary Key: user_id

Orders

Column Name	Type	Description
`order_id` PK	int	Primary key, unique order identifier
`order_date`	date	Date when order was placed
`item_id`	int	Foreign key referencing Items table
`buyer_id`	int	Foreign key referencing Users table
`seller_id`	int	Foreign key referencing Users table

Primary Key: order_id

Items

Column Name	Type	Description
`item_id` PK	int	Primary key, unique item identifier
`item_brand`	varchar	Brand name of the item

Primary Key: item_id

Input & Output

Example 1 — Mixed Results

Input Tables:

Users

user_id	join_date	favorite_brand
1	2019-01-01	Lenovo
2	2019-02-09	Samsung
3	2019-01-19	LG

Orders

order_id	order_date	item_id	buyer_id	seller_id
1	2019-08-01	4	1	2
2	2019-08-02	2	1	3
3	2019-08-03	3	2	3
4	2019-08-04	1	4	2
5	2019-08-04	1	3	4

Items

item_id	item_brand
1	Samsung
2	Lenovo
3	LG
4	HP

Output:

seller_id	2nd_item_fav_brand
1	no
2	yes
3	no
4	no

💡 Note:

User 1 has no sales, so result is 'no'. User 2 sold HP first (2019-08-01) and Samsung second (2019-08-04) - Samsung matches their favorite brand, so 'yes'. User 3 sold Lenovo first (2019-08-02) and LG second (2019-08-03) - LG matches their favorite brand, but wait... User 3's favorite is LG and second item is LG, so should be 'yes'. User 4 only sold one item, so 'no'.

Example 2 — Single Sale Users

Input Tables:

Users

user_id	join_date	favorite_brand
1	2019-01-01	Apple
2	2019-02-09	Samsung

Orders

order_id	order_date	item_id	buyer_id	seller_id
1	2019-08-01	1	2	1

Items

item_id	item_brand
1	Apple

Output:

seller_id	2nd_item_fav_brand
1	no
2	no

💡 Note:

User 1 sold only one item (Apple), so even though it matches their favorite brand, we need the second item - result is 'no'. User 2 made no sales, so result is 'no'.

Constraints

1 ≤ Users.user_id ≤ 1000
join_date and order_date are valid dates
No seller sells more than one item on the same day
All foreign key references are valid

Visualization

Tap to expand

Understanding the Visualization

Join Tables

Combine Orders and Items to get brands

Rank Sales

Use ROW_NUMBER() to order sales by date

Filter Second

Keep only rank = 2 items

Compare Brands

Check if second item brand matches favorite

Key Takeaway

🎯 Key Insight: Use ROW_NUMBER() window function to identify chronologically ordered sales and handle users with insufficient sales data

Asked in

a Amazon 15 f Facebook 8 M Microsoft 5

Use ROW_NUMBER() window function to rank each seller's items chronologically, then LEFT JOIN with Users table to compare the second item's brand with their favorite brand preference.

Table Schema

Users

Column Name	Type	Description
`user_id` PK	int	Primary key, unique user identifier
`join_date`	date	Date when user joined the platform
`favorite_brand`	varchar	User's favorite brand preference

Primary Key: user_id

Orders

Column Name	Type	Description
`order_id` PK	int	Primary key, unique order identifier
`order_date`	date	Date when order was placed
`item_id`	int	Foreign key referencing Items table
`buyer_id`	int	Foreign key referencing Users table
`seller_id`	int	Foreign key referencing Users table

Primary Key: order_id

Items

Column Name	Type	Description
`item_id` PK	int	Primary key, unique item identifier
`item_brand`	varchar	Brand name of the item

Primary Key: item_id

Common Approaches

Approach	Time	Space	Notes
✓ Window Function with ROW_NUMBER	O(n log n)	O(n)	Use ROW_NUMBER() to rank sales chronologically and identify the second item sold

Window Function with ROW_NUMBER — Algorithm Steps

Step 1: Join Orders with Items to get item brands for each sale
Step 2: Use ROW_NUMBER() OVER (PARTITION BY seller_id ORDER BY order_date) to rank sales
Step 3: Filter for rank = 2 to get only second items sold
Step 4: LEFT JOIN with Users to include all users and compare brands

Visualization

Tap to expand

Step-by-Step Walkthrough

Partition

Group by seller_id

Order

Sort by order_date

Rank

Assign ROW_NUMBER()

Filter

Keep only rank = 2

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting required for ROW_NUMBER() window function

⚡ Linearithmic

Space Complexity

O(n)

Storage for intermediate results and window frames

⚡ Linearithmic Space

28.5K Views

Medium Frequency

~18 min Avg. Time

892 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

Window Function with ROW_NUMBER — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler