Amazon-style Company ChallengeHardVerified answerSQLite live

Seller Review Coverage Report

For each seller, show how many products they list, how many total reviews their products have received, and what percentage of those reviews are verified purchases.

  • Joins
  • Aggregation
  • Numeric functions
  • NULL handling
  • Sorting

Challenge brief

Understand the request

Marketplace Trust Team is auditing seller quality metrics ahead of a trust-and-safety review, including how many of their reviews are verified purchases.

Build a seller review quality audit showing product count, review volume, and verified purchase percentage.

Return

  • seller_name
  • country
  • seller_rating (platform rating)
  • listed_products
  • total_reviews
  • verified_pct (% of reviews that are verified, rounded to 1 decimal; 0 if no reviews)

Constraints

  • Include sellers with zero reviews
  • Count each listed product once even when it has several reviews
  • Verified coverage is verified reviews divided by all reviews and displays zero when no reviews exist
  • Order by total reviews ascending, then 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
  • verified_purchaseINTEGER

Hints, when you need them

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

Hint 1

Path: sellers → products (INNER JOIN for their listings) → reviews (LEFT JOIN so zero-review sellers appear). You need to aggregate per seller: count products, count reviews, and compute a percentage with divide-by-zero protection.

Hint 2

Count distinct product IDs because review rows repeat product IDs. Protect the review-count denominator with NULLIF and display zero with COALESCE.

Hint 3

Scaffold: preserve products without reviews, group at seller grain, count distinct listings and review rows separately, and protect the verified-review denominator.

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(DISTINCT p.product_id) AS listed_products, COUNT(r.review_id) AS total_reviews, ROUND(COALESCE(SUM(r.verified_purchase) * 100.0 / NULLIF(COUNT(r.review_id), 0), 0), 1) AS verified_pct FROM sellers s INNER JOIN products p ON s.seller_id = p.seller_id LEFT JOIN reviews r ON p.product_id = r.product_id GROUP BY s.seller_id, s.seller_name, s.country, s.rating ORDER BY total_reviews ASC, s.seller_name;

Why this works

COUNT(DISTINCT product_id) keeps the listing count at product grain even when a product has several reviews. NULLIF protects the verified-review denominator, and COALESCE displays zero for sellers with no reviews. The overlay's unreviewed TechGear listing verifies that review joins cannot inflate product counts.

Success check

4 sellers — BookWorld (0 reviews, 0%), then the other 3 each with 100% verified

Expected result

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

seller_namecountryseller_ratinglisted_productstotal_reviewsverified_pct
BookWorldUK4.9100
GlobalElectronicsChina4.211100
HomeEssentialsUSA4.534100
TechGear ProUSA4.844100

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.