Amazon-style Company ChallengeMediumVerified answerSQLite live

Sellers with No Customer Reviews

Which sellers have products listed on Amazon but have received zero customer reviews across all their products?

  • Joins
  • Subqueries
  • Aggregation
  • Filtering
  • Sorting

Challenge brief

Understand the request

Marketplace Trust Team is auditing seller visibility — sellers with no reviews may be new or low-exposure and need promotion support.

Find sellers whose products have received no customer reviews at all.

Return

  • seller_name
  • country
  • seller_rating (platform rating)
  • product_count (number of products listed)

Constraints

  • A seller qualifies only if NONE of their products have any review
  • Include product count to show how many listings they have
  • A seller with both reviewed and unreviewed products must be excluded
  • Order by seller_name

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

sellers

  • seller_idINTEGER
  • seller_nameVARCHAR(100)
  • countryVARCHAR(50)
  • ratingREAL

products

  • product_idINTEGER
  • seller_idINTEGER

reviews

  • review_idINTEGER
  • product_idINTEGER

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

The condition applies to the seller's entire product set. Checking only for an unreviewed product is too weak when a seller has a mix of reviewed and unreviewed listings.

Hint 2

Join sellers to their products for the listing count, then use a correlated NOT EXISTS to reject any seller whose product set joins to a review.

Hint 3

Scaffold: join sellers to listings for the count, then add a correlated NOT EXISTS over that seller's complete product-to-review path before grouping.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT s.seller_name, s.country, s.rating AS seller_rating, COUNT(p.product_id) AS product_count FROM sellers s INNER JOIN products p ON s.seller_id = p.seller_id WHERE NOT EXISTS (SELECT 1 FROM products reviewed_product INNER JOIN reviews r ON r.product_id = reviewed_product.product_id WHERE reviewed_product.seller_id = s.seller_id) GROUP BY s.seller_id, s.seller_name, s.country, s.rating ORDER BY s.seller_name;

Why this works

The seller-level NOT EXISTS check rejects a seller as soon as any listed product has a review. The additional product-level check leaves only that seller's unreviewed listings to count. This prevents a seller with a mix of reviewed and unreviewed products from being misclassified; only BookWorld qualifies.

Success check

1 seller — BookWorld (UK, rating 4.9, 1 product with no reviews)

Expected result

Use this output to verify values, aliases, ordering, and row count.

seller_namecountryseller_ratingproduct_count
BookWorldUK4.91

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.