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.
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.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Join + Conditional Aggregation | O(n) | O(1) | Best for analytical SQL queries where you check multiple product conditions per buyer using GROUP BY. |
| Subquery with NOT EXISTS / NOT IN | O(n) | O(n) | Useful when expressing set exclusion logic or when separating inclusion and exclusion queries improves readability. |
LeetCode 1083: Sales Analysis II [SQL] • Frederik Müller • 3,550 views views
Watch 2 more video solutions →Practice Sales Analysis II with our built-in code editor and test cases.
Practice on FleetCode