The Scorched Earth Reviews

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Yelp

Problem

Every product stuck at the very bottom of the rating scale, a rating of exactly 1, is headed into the quality assurance team's review for a shared defect. List those products from the catalog with each one's name and rating.

Someone left a trail of one-star wreckage. Round up every product they burned.

Tables

products

Columns: product_id INTEGER, product_name TEXT, category TEXT, price REAL, rating REAL, in_stock INTEGER

Expected output

Worked solution and explanation

What this is really testing

This is a row-set filter wearing a defect-hunt costume. The skill being probed: read 'the bottom of the rating scale' as a fixed floor and keep every product sitting on it with rating = 1. The trap is treating 'lowest' as something to look up. ORDER BY rating LIMIT 1 hands back one product when four share the floor, and a looser rating < 2 drags in the 1.5s and 1.9s that are low but not the bottom. Either way QA chases the wrong list: a single unit instead of the shared defect, or a defect list padded with products that never hit 1.

Break down the requirements

Step 1: Filter to exactly rating = 1

The floor of the scale is an exact value, not a band and not a single winner. WHERE rating = 1 keeps every product at rock bottom and nothing above it. Because rating is a REAL, the literal 1 compares equal to the stored 1.0, so no cast is needed.

Step 2: Return product_name and rating

SELECT product_name, rating returns the two fields the review needs and nothing else. There is nothing to group or aggregate: every qualifying row is its own answer, and there can be several of them.

The solution

SELECT product_name, rating
FROM products
WHERE rating = 1

Looking up the lowest

ORDER BY rating LIMIT 1 treats the floor as a single winner. On this data four products sit at 1.0, so it returns one and silently drops three.

Matching the floor

WHERE rating = 1 treats the floor as a known value and returns every product on it: all four rows, and still correct if tomorrow there are forty.

Common follow-up questions

• Some rows have a NULL `rating`. Are they included or excluded by your filter, and is that what you want? (Tests NULL-comparison awareness: `NULL = 1` is never true, so unrated products drop out.)
• How would you verify there are no duplicate product rows inflating this result? (Tests data-quality awareness without assuming any aggregation exists.)
• If this ran against billions of product rows, what index would keep the `rating = 1` lookup cheap? (Tests practical indexing decisions for a selective equality filter at scale.)