Market Analysis II - Solution & Explanation
Problem Statement
Table: Users
+----------------+---------+ | Column Name | Type | +----------------+---------+ | user_id | int | | join_date | date | | favorite_brand | varchar | +----------------+---------+ user_id is the primary key (column with unique values) of this table. This table has the info of the users of an online shopping website where users can sell and buy items.
Table: Orders
+---------------+---------+ | Column Name | Type | +---------------+---------+ | order_id | int | | order_date | date | | item_id | int | | buyer_id | int | | seller_id | int | +---------------+---------+ order_id is the primary key (column with unique values) of this table. item_id is a foreign key (reference column) to the Items table. buyer_id and seller_id are foreign keys to the Users table.
Table: Items
+---------------+---------+ | Column Name | Type | +---------------+---------+ | item_id | int | | item_brand | varchar | +---------------+---------+ item_id is the primary key (column with unique values) of this table.
Write a solution to find for each user whether the brand of the second item (by date) they sold is their favorite brand. If a user sold less than two items, report the answer for that user as no. It is guaranteed that no seller sells more than one item in a day.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Users table: +---------+------------+----------------+ | user_id | join_date | favorite_brand | +---------+------------+----------------+ | 1 | 2019-01-01 | Lenovo | | 2 | 2019-02-09 | Samsung | | 3 | 2019-01-19 | LG | | 4 | 2019-05-21 | HP | +---------+------------+----------------+ Orders table: +----------+------------+---------+----------+-----------+ | 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 | | 6 | 2019-08-05 | 2 | 2 | 4 | +----------+------------+---------+----------+-----------+ Items table: +---------+------------+ | 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 | +-----------+--------------------+ Explanation: The answer for the user with id 1 is no because they sold nothing. The answer for the users with id 2 and 3 is yes because the brands of their second sold items are their favorite brands. The answer for the user with id 4 is no because the brand of their second sold item is not their favorite brand.
Approach Overview
Problem Overview: You have three tables: Users, Orders, and Items. For every user acting as a seller, determine whether the second item they sold matches their favorite_brand. If the seller never sold a second item, the answer should be no.
Approach 1: Window Function Ranking (ROW_NUMBER) (Time: O(n log n), Space: O(n))
The cleanest solution ranks each seller's orders by sale date. Use a window function such as ROW_NUMBER() OVER (PARTITION BY seller_id ORDER BY order_date) to assign a sequential rank to every order per seller. Filter rows where the rank equals 2 to isolate the second item sold by each seller. Join this result with the Items table to get the item's brand and compare it against the seller's favorite_brand from the Users table. Finally, left join this ranked dataset back to Users so sellers with fewer than two sales still appear, returning 'no' for them. The ranking step requires sorting orders per seller, which typically costs O(n log n). This approach is concise and relies on standard SQL and database windowing features.
Approach 2: Correlated Subquery with Order Counting (Time: O(n²) worst case, Space: O(1))
Another method avoids window functions by identifying the second sale through counting logic. For each order, run a correlated subquery that counts how many orders exist for the same seller with an earlier or equal date. When the count equals 2, that row represents the second item sold. Join that order with the Items table to retrieve the brand and compare it to the seller's favorite brand. While this works in SQL engines without window functions, it repeatedly scans the orders table and can degrade to O(n²) time. It’s mostly useful when practicing alternative query patterns in SQL.
Recommended for interviews: The window function approach using ROW_NUMBER is what most interviewers expect. It clearly shows you understand partitioning, ranking, and joins—core patterns in analytical SQL problems. The correlated subquery demonstrates logical reasoning but is less efficient and rarely preferred in production queries.
Solution
Code
MySQL
Detailed Complexity Analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Window Function Ranking (ROW_NUMBER) | O(n log n) | O(n) | Best general solution when the SQL engine supports window functions |
| Correlated Subquery with Order Counting | O(n²) worst case | O(1) | Fallback approach when window functions are unavailable |
Video Solution
Leetcode Hard Problem 3 | Market Analysis 2 | Complex SQL 9 • Ankit Bansal • 35,747 views views
Watch 2 more video solutions →Frequently Asked Questions
Is Market Analysis II easy or hard?
Market Analysis II Python/Java solution
How to solve Market Analysis II in O(n)?
What is the best approach for Market Analysis II?
Is Market Analysis II asked at Google/Amazon/Meta?
What data structure is used in Market Analysis II?
What is the time complexity of Market Analysis II?
Ready to solve this problem?
Practice Market Analysis II with our built-in code editor and test cases.
Practice on FleetCode