Sales Analysis II - Problem

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
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
Sales Analysis II: Product Purchase PatternsProduct Tableidname1S82iPhoneSales Tableproduct_idbuyer_id112114JOIN & FILTERResult: S8 Onlybuyer_id4Buyer 1: S8 ✓ + iPhone ✓ → ExcludedBuyer 4: S8 ✓ + iPhone ✗ → Included
Understanding the Visualization
1
Join Tables
Connect Sales and Product tables
2
Filter Products
Identify S8 and iPhone buyers separately
3
Set Operation
Exclude iPhone buyers from S8 buyers
Key Takeaway
🎯 Key Insight: Use set operations or correlated subqueries to handle complex inclusion/exclusion patterns in relational data

Related Problems

Asked in
Amazon 15 Facebook 12 Microsoft 8
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