Sales Analysis II

Sales Analysis II - Problem

Database Easy

You are given two tables: Product and Sales.

The Product table contains information about products including their unique ID, name, and unit price. The Sales table records all sales transactions with seller ID, product ID, buyer ID, sale date, quantity, and price.

Task: Find all buyers who have bought S8 but have not bought iPhone.

Return the result in any order.

Table Schema

Product

Column Name	Type	Description
`product_id` PK	int	Primary key, unique product identifier
`product_name`	varchar	Name of the product (e.g., S8, iPhone)
`unit_price`	int	Price per unit of the product

Primary Key: product_id

Sales

Column Name	Type	Description
`seller_id`	int	ID of the seller
`product_id`	int	Foreign key referencing Product.product_id
`buyer_id`	int	ID of the buyer (never NULL)
`sale_date`	date	Date of the sale (never NULL)
`quantity`	int	Quantity of products sold
`price`	int	Total price of the sale

Input & Output

Example 1 — S8 and iPhone Purchases

Input Tables:

Product

product_id	product_name	unit_price
1	S8	1000
2	iPhone	1200
3	iPad	800

Sales

seller_id	product_id	buyer_id	sale_date	quantity	price
1	1	1	2019-01-21	2	2000
1	2	1	2019-02-17	1	800
2	1	2	2019-06-02	1	800
3	3	3	2019-05-13	2	2800
4	1	4	2019-05-13	1	1000

Output:

buyer_id
2
4

💡 Note:

Buyer 1 bought both S8 (product_id=1) and iPhone (product_id=2), so they are excluded. Buyer 2 bought S8 but not iPhone, however they don't exist in this example. Buyer 4 bought only S8 and never bought iPhone, so they are included in the result.

Example 2 — No Valid Buyers

Input Tables:

Product

product_id	product_name	unit_price
1	S8	1000
2	iPhone	1200

Sales

seller_id	product_id	buyer_id	sale_date	quantity	price
1	1	1	2019-01-21	1	1000
1	2	1	2019-02-17	1	1200
2	2	2	2019-06-02	1	1200

Output:

buyer_id

💡 Note:

Buyer 1 bought both S8 and iPhone, so they are excluded. Buyer 2 bought only iPhone but not S8, so they don't qualify. No buyers meet the criteria of buying S8 but not iPhone.

Constraints

1 ≤ product_id ≤ 100
1 ≤ seller_id, buyer_id ≤ 1000
product_name consists of lowercase letters, digits, and spaces
buyer_id is never NULL
sale_date is never NULL

Visualization

Tap to expand

Asked in

A Amazon 15 F Facebook 12 M Microsoft 8

Use set operations like EXCEPT or correlated subqueries with NOT EXISTS to find buyers who purchased S8 but exclude those who also bought iPhone.

Table Schema

Product

Column Name	Type	Description
`product_id` PK	int	Primary key, unique product identifier
`product_name`	varchar	Name of the product (e.g., S8, iPhone)
`unit_price`	int	Price per unit of the product

Primary Key: product_id

Sales

Column Name	Type	Description
`seller_id`	int	ID of the seller
`product_id`	int	Foreign key referencing Product.product_id
`buyer_id`	int	ID of the buyer (never NULL)
`sale_date`	date	Date of the sale (never NULL)
`quantity`	int	Quantity of products sold
`price`	int	Total price of the sale

Common Approaches

✓ NOT EXISTS Subquery

⏱️ Time: O(n * m) Space: O(1)

Get all buyers who purchased S8, then use NOT EXISTS with a correlated subquery to exclude buyers who also purchased iPhone. This approach is more widely supported across database systems.

Set Difference with EXCEPT

⏱️ Time: O(n + m) Space: O(k)

First query gets all buyers who bought S8, then subtract buyers who bought iPhone using EXCEPT. This efficiently handles the exclusion logic in a clean, readable way.

NOT EXISTS Subquery — Algorithm Steps

Step 1: Find all buyers who bought S8 products
Step 2: For each S8 buyer, check if they also bought iPhone
Step 3: Keep only buyers who have no iPhone purchases

Visualization

Tap to expand

Step-by-Step Walkthrough

Get S8 Buyers

Main query finds buyers who purchased S8

Check iPhone

Subquery checks if same buyer bought iPhone

Filter Results

NOT EXISTS excludes buyers with iPhone purchases

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n * m)

For each S8 buyer, check iPhone purchases

✓ Linear Growth

Space Complexity

O(1)

Constant space for subquery execution

✓ Linear Space

28.5K Views

Medium Frequency

~12 min Avg. Time

890 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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

NOT EXISTS Subquery — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler