Market Analysis II - Problem

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
4 2019-03-15 HP
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 yes
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 LG, so '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
Market Analysis II - Solution Visualization INPUT TABLES Users Table: user_id | favorite_brand 1 | Lenovo 2 | Samsung 3 | Apple Orders Table: order_id|seller_id|item_brand|date 1 |1 |Samsung |2019-08-01 2 |1 |Lenovo |2019-08-02 3 |2 |Apple |2019-08-01 4 |2 |Samsung |2019-08-03 5 |3 |Apple |2019-08-04 Items Table: item_id | item_brand (brands linked to orders) ALGORITHM STEPS 1 Rank Orders by Date Use ROW_NUMBER() OVER (PARTITION BY seller_id ORDER BY order_date) 2 Filter 2nd Items WHERE rank = 2 Get 2nd sold item only 3 Join with Users LEFT JOIN to keep all users in result 4 Compare Brands IF item_brand = favorite_brand: 'yes' ELSE: 'no' User1: Samsung(1) Lenovo(2) 2nd item = Lenovo = fav? yes FINAL RESULT Output Table: seller_id | 2nd_item_fav 1 | yes 2 | no 3 | no User 1: 2nd item Lenovo = favorite Lenovo [OK yes] User 2: 2nd item Samsung != favorite Samsung [OK no] User 3: Only 1 item sold Less than 2 items sold Result: 'no' (default) Key Insight: Use ROW_NUMBER() window function to rank each seller's orders chronologically. Filter for rank=2 to get the second item sold. LEFT JOIN ensures users with fewer than 2 sales appear with 'no' result. Compare item_brand with favorite_brand using CASE WHEN for the final yes/no determination. TutorialsPoint - Market Analysis II | Optimal Solution

Related Problems

Asked in
Amazon 15 Facebook 8 Microsoft 5
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