Skip to main content
Talentd

Sales Analysis II - Solution & Explanation

EasyPremiumFree on FleetCodeDatabase4 min readAsked at: Amazon
Practice this problem

Problem Statement

Table: Product

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
| unit_price   | int     |
+--------------+---------+
product_id is the primary key (column with unique values) of this table.
Each row of this table indicates the name and the price of each product.

Table: Sales

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| seller_id   | int     |
| product_id  | int     |
| buyer_id    | int     |
| sale_date   | date    |
| quantity    | int     |
| price       | int     |
+-------------+---------+
This table might have repeated rows.
product_id is a foreign key (reference column) to the Product table.
buyer_id is never NULL. 
sale_date is never NULL. 
Each row of this table contains some information about one sale.

 

Write a solution to report the buyers who have bought S8 but not iPhone. Note that S8 and iPhone are products presented in the Product table.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Product table:
+------------+--------------+------------+
| product_id | product_name | unit_price |
+------------+--------------+------------+
| 1          | S8           | 1000       |
| 2          | G4           | 800        |
| 3          | iPhone       | 1400       |
+------------+--------------+------------+
Sales table:
+-----------+------------+----------+------------+----------+-------+
| seller_id | product_id | buyer_id | sale_date  | quantity | price |
+-----------+------------+----------+------------+----------+-------+
| 1         | 1          | 1        | 2019-01-21 | 2        | 2000  |
| 1         | 2          | 2        | 2019-02-17 | 1        | 800   |
| 2         | 1          | 3        | 2019-06-02 | 1        | 800   |
| 3         | 3          | 3        | 2019-05-13 | 2        | 2800  |
+-----------+------------+----------+------------+----------+-------+
Output: 
+-------------+
| buyer_id    |
+-------------+
| 1           |
+-------------+
Explanation: The buyer with id 1 bought an S8 but did not buy an iPhone. The buyer with id 3 bought both.

Approach Overview

Problem Overview: Given Sales and Product tables, identify buyers who purchased the product S8 but never purchased an iPhone. The result should return the buyer_id for those customers only.

Approach 1: Join + Conditional Aggregation (O(n) time, O(1) extra space)

Join the Sales and Product tables on product_id so you can access product names for every purchase. After the join, group rows by buyer_id. Inside the HAVING clause, use conditional aggregation to check whether a buyer purchased S8 at least once while ensuring they never purchased iPhone. This is typically implemented using expressions such as SUM(product_name = 'S8') and SUM(product_name = 'iPhone'). If the first count is greater than zero and the second equals zero, the buyer qualifies. The database performs a single scan of the joined dataset, making this approach efficient and easy to read.

This technique is common in analytical SQL problems where you need to verify the presence of one category while excluding another within the same group. It relies heavily on GROUP BY behavior and boolean evaluation inside aggregation functions. Problems like this frequently appear in interview rounds focused on SQL and database querying.

Approach 2: Subquery with NOT EXISTS / NOT IN (O(n) time, O(n) subquery space)

Another option is filtering buyers who purchased S8 while excluding any buyer who appears in a subquery of iPhone purchasers. First, find all buyers who bought the S8 product by joining Sales with Product and filtering with product_name = 'S8'. Then exclude buyers returned from a subquery that selects buyers who purchased an iPhone. This can be implemented with NOT IN or NOT EXISTS. The logic is straightforward: include S8 buyers and remove anyone who appears in the iPhone list.

This pattern is useful when filtering across mutually exclusive product conditions. It also appears frequently in SQL interview questions involving set differences or anti-joins. Understanding how NOT EXISTS behaves with correlated queries is valuable when working with relational filtering and joins.

Recommended for interviews: The join with conditional aggregation is usually preferred. It keeps the entire condition inside one grouped query and clearly expresses both requirements: "must have S8" and "must not have iPhone." Interviewers like this approach because it demonstrates strong understanding of SQL grouping and filtering logic. The subquery approach still works and shows familiarity with set-based filtering, but the aggregation version tends to be more concise and performant in analytical queries.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Join + Conditional AggregationO(n)O(1)Best for analytical SQL queries where you check multiple product conditions per buyer using GROUP BY.
Subquery with NOT EXISTS / NOT INO(n)O(n)Useful when expressing set exclusion logic or when separating inclusion and exclusion queries improves readability.

Video Solution

LeetCode 1083: Sales Analysis II [SQL] • Frederik Müller • 3,577 views views

Watch 2 more video solutions →

Frequently Asked Questions

Is Sales Analysis II easy or hard?
Sales Analysis II is classified as Easy on LeetCode. The difficulty mainly comes from recognizing that conditional aggregation or a NOT EXISTS filter can enforce the requirement of buying one product while excluding another.
Sales Analysis II Python/Java solution
Sales Analysis II is a database problem designed to be solved with SQL rather than Python or Java. The expected solution uses MySQL queries involving JOIN, GROUP BY, and HAVING conditions to filter the correct buyers.
How to solve Sales Analysis II in O(n)?
Join Sales with Product on product_id, group by buyer_id, and apply conditional aggregation in the HAVING clause. Ensure the buyer has at least one purchase of 'S8' and zero purchases of 'iPhone'. The database processes rows in a single pass during grouping, resulting in O(n) time complexity.
What is the best approach for Sales Analysis II?
The most common solution uses a JOIN between Sales and Product followed by GROUP BY buyer_id with conditional aggregation in the HAVING clause. You count purchases of 'S8' and ensure purchases of 'iPhone' equal zero. This runs in O(n) time with a single grouped scan and is typically the cleanest SQL solution.
Is Sales Analysis II asked at Google/Amazon/Meta?
SQL filtering and aggregation problems similar to Sales Analysis II appear frequently in data engineering and analytics interviews at companies like Amazon, Meta, and Google. These interviews often test joins, grouping, and conditional filtering logic.
What data structure is used in Sales Analysis II?
The problem relies on relational database tables and SQL operations rather than traditional data structures. Internally, the database engine uses hash or sort-based grouping to perform GROUP BY and aggregation operations efficiently.
What is the time complexity of Sales Analysis II?
Most SQL solutions run in O(n) time where n is the number of rows processed after joining Sales and Product. The database scans the joined dataset and groups rows by buyer_id. Aggregation and filtering happen during the grouping stage.

Ready to solve this problem?

Practice Sales Analysis II with our built-in code editor and test cases.

Practice on FleetCode
Previous

Sales Analysis I

Next

Sales Analysis III