Market Analysis III - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase5 min read

Problem Statement

Table: Users

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| seller_id      | int     |
| join_date      | date    |
| favorite_brand | varchar |
+----------------+---------+
seller_id is column of unique values for this table.
This table contains seller id, join date, and favorite brand of sellers.

Table: Items

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| item_id       | int     |
| item_brand    | varchar |
+---------------+---------+
item_id is the column of unique values for this table.
This table contains item id and item brand.

Table: Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| order_date    | date    |
| item_id       | int     |
| seller_id     | int     |
+---------------+---------+
order_id is the column of unique values for this table.
item_id is a foreign key to the Items table.
seller_id is a foreign key to the Users table.
This table contains order id, order date, item id and seller id.

Write a solution to find the top seller who has sold the highest number of unique items with a different brand than their favorite brand. If there are multiple sellers with the same highest count, return all of them.

Return the result table ordered by seller_id in ascending order.

The result format is in the following example.

Example 1:

Input: 
Users table:
+-----------+------------+----------------+
| seller_id | join_date  | favorite_brand |
+-----------+------------+----------------+
| 1         | 2019-01-01 | Lenovo         |
| 2         | 2019-02-09 | Samsung        |
| 3         | 2019-01-19 | LG             |
+-----------+------------+----------------+
Orders table:
+----------+------------+---------+-----------+
| order_id | order_date | item_id | seller_id |
+----------+------------+---------+-----------+
| 1        | 2019-08-01 | 4       | 2         |
| 2        | 2019-08-02 | 2       | 3         |
| 3        | 2019-08-03 | 3       | 3         |
| 4        | 2019-08-04 | 1       | 2         |
| 5        | 2019-08-04 | 4       | 2         |
+----------+------------+---------+-----------+
Items table:
+---------+------------+
| item_id | item_brand |
+---------+------------+
| 1       | Samsung    |
| 2       | Lenovo     |
| 3       | LG         |
| 4       | HP         |
+---------+------------+
Output: 
+-----------+-----------+
| seller_id | num_items |
+-----------+-----------+
| 2         | 1         |
| 3         | 1         |
+-----------+-----------+
Explanation: 
- The user with seller_id 2 has sold three items, but only two of them are not marked as a favorite. We will include a unique count of 1 because both of these items are identical.
- The user with seller_id 3 has sold two items, but only one of them is not marked as a favorite. We will include just that non-favorite item in our count.
Since seller_ids 2 and 3 have the same count of one item each, they both will be displayed in the output.

Approach Overview

Problem Overview: The task asks you to analyze marketplace data and determine whether the second item sold by each seller matches their favorite brand. The data is spread across multiple tables (typically Users, Orders, and Items), so the solution relies on joining tables, identifying the second order for each seller, and comparing the item brand with the seller’s favorite brand.

Approach 1: Equijoin + Grouping + Subquery (O(n log n) time, O(n) space)

This approach joins the marketplace tables using equijoins to combine seller information, order history, and item brand data. First, create a subquery that determines the chronological order of items sold by each seller. This is typically done by grouping orders by seller_id and identifying the second order using an ordered condition (for example, filtering by the second smallest order_date or using ranking logic). Once the second order per seller is identified, join it with the Items table to fetch the corresponding item_brand.

Next, compare that brand with the seller’s favorite_brand from the Users table. The outer query performs the final projection, returning each seller and whether the brand of the second sold item matches their favorite brand. Sellers with fewer than two orders are handled naturally by the subquery logic and return a negative result. The heavy lifting is done by the subquery that isolates the second transaction for each seller.

The key insight is separating the problem into two phases: first identify the second order per seller, then evaluate the brand match. SQL grouping and filtering make this efficient without scanning the dataset multiple times. Since joins are performed on indexed keys like user_id, seller_id, and item_id, the query scales well for large datasets.

Conceptually, this solution uses relational operations such as SQL Join, grouping with aggregation via GROUP BY, and filtering with Subqueries. These patterns appear frequently in database interview questions where you must isolate a specific row within grouped records.

Recommended for interviews: Interviewers expect a clean SQL solution using joins and a subquery that isolates the second order per seller. A brute-force mindset—scanning all orders and manually filtering—shows the basic idea, but the grouped subquery demonstrates real SQL fluency and the ability to express relational logic concisely.

Solution

We can use equijoin to connect the Orders table and the Users table according to seller_id, then connect Items according to item_id, and filter out the records where item_brand is not equal to favorite_brand. Then, group by seller_id and count the number of item_id corresponding to each seller_id. Finally, use a subquery to find the seller_id with the most item_id.

Code

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

Approach	Time	Space	When to Use
Equijoin + Grouping + Subquery	O(n log n)	O(n)	General SQL solution when multiple tables must be combined and a specific ranked record per group is required
Join with Ordered Filtering	O(n log n)	O(n)	When order dates must be sorted to determine the second transaction per seller

Video Solution

Leetcode MEDIUM 2922 - Market Analysis III - Using DENSE_RANK Window in SQL | Everyday Data Science • Everyday Data Science • 519 views views

Frequently Asked Questions

Is Market Analysis III easy or hard?

Market Analysis III is considered a Medium difficulty SQL problem. The challenge lies in correctly identifying the second order per seller and joining multiple tables without duplicating rows.

Market Analysis III Python/Java solution

This problem is primarily designed for SQL rather than Python or Java because it operates directly on relational tables. While the logic could be reproduced using data processing libraries, the intended solution uses MySQL joins and subqueries.

How to solve Market Analysis III in O(n)?

A near O(n) approach is possible when proper indexes exist on seller_id, item_id, and order_date. The database can then resolve joins and grouping efficiently, scanning the orders once to determine the second order per seller before performing the brand comparison.

What is the best approach for Market Analysis III?

The most effective approach uses SQL equijoins combined with grouping and a subquery to isolate the second order for each seller. After identifying the second transaction, you join it with the Items table and compare the item_brand with the seller’s favorite_brand. This avoids scanning unnecessary rows and keeps the query concise.

Is Market Analysis III asked at Google/Amazon/Meta?

Problems involving joins, grouped filtering, and identifying the nth record per group are common in SQL interview rounds at companies like Amazon and Meta. Variations of marketplace analytics queries frequently appear in data engineer and backend interviews.

What data structure is used in Market Analysis III?

The problem relies on relational database structures rather than in-memory data structures. Internally, SQL engines use indexed tables, join operations, grouping, and subqueries to process the dataset efficiently.

What is the time complexity of Market Analysis III?

The typical SQL solution runs in about O(n log n) time because the database engine may sort orders by date or apply grouped ordering per seller. Join operations are usually O(n) with indexed keys, while grouping and ordering dominate the cost.

Ready to solve this problem?

Practice Market Analysis III with our built-in code editor and test cases.

Practice on FleetCode

Combine Two Tables

Second Highest Salary

Problem Info

DifficultyMedium

Acceptance41.1%

Approaches1

Reading time5 min

Practice this problem

Open in Editor