Skip to content

LeetCode 1083: Sales Analysis II

A clear explanation of finding buyers who bought an iPhone but not an iPad using JOIN and NOT IN filtering.

leetcode database

Problem Restatement

We have Sales(seller_id, product_id, buyer_id, sale_date, quantity, price) and Product(product_id, product_name, unit_price).

Find buyers who bought the product 'S8' but not the product 'iPhone'.

Edge Cases

  • Match the aggregate to the question: use raw COUNT or SUM for rows/amounts, and COUNT(DISTINCT ...) only for unique entities.
  • Tie cases should return every qualifying row unless the statement asks for a single row.
  • For outer joins, keep filters on the joined table in the ON clause when unmatched rows must remain visible.

Common Pitfalls

  • Do not put aggregate filters in WHERE; use HAVING after GROUP BY.
  • When the answer needs all tied winners, avoid LIMIT 1 unless the statement explicitly asks for one row.

Implementation 

SELECT DISTINCT s.buyer_id
FROM Sales s
JOIN Product p ON s.product_id = p.product_id
WHERE p.product_name = 'S8'
  AND s.buyer_id NOT IN (
      SELECT s2.buyer_id
      FROM Sales s2
      JOIN Product p2 ON s2.product_id = p2.product_id
      WHERE p2.product_name = 'iPhone'
  );

Code Explanation

The main query finds buyers of 'S8'.

The subquery finds buyers of 'iPhone'.

NOT IN excludes buyers who purchased both products.

DISTINCT avoids duplicate buyer IDs if they bought 'S8' multiple times.