The Merit Circle

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

Domain

SQL

Difficulty

medium

Seniority

L3

Asked in

Google

Problem

A quality team is reviewing the products customers rate worst, reading the rating column that already holds each product's average; products still waiting on their first rating are left out of the review. Return the ten lowest-rated products, weakest first, and when two share the same average settle the order alphabetically by product name so the earlier name claims the last spot.

Ten seats at the top, and the ratings decide who sits.

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 problem is really testing

This is a bottom-N leaderboard where the two things people forget are exactly what the grader checks: the NULL filter and the tie-break. Because the sort is ascending, the ~5% of products with a NULL rating sort to the very TOP in SQLite (NULL ranks below every real value), so skipping WHERE rating IS NOT NULL fills your ten slots with products that have no rating at all. And with only ~50 distinct rating values across 15,000 rows, the bottom of the scale is one big tied block; ORDER BY rating ASC alone leaves which ten surface, and in what order, up to the engine. The other tell is the schema: rating is ALREADY a per-product average, so anyone reaching for AVG and GROUP BY wrote SQL before reading the table.

Break down the requirements

Step 1: Read the schema before you aggregate

The prose says the rating column already reflects each product's average, so there is nothing to aggregate: no AVG, no GROUP BY. Reading that one clause is half the exercise.

Step 2: Keep unrated products out

About 5% of ratings are NULL, and in ascending order SQLite sorts those NULL rows FIRST, right where you are trying to read the ten worst real ratings. Drop them with WHERE rating IS NOT NULL so an unrated product cannot occupy a slot. Note that rating = NULL never works: it returns UNKNOWN for every row.

Step 3: Sort ascending, break ties, then cut to ten

Order by rating ASC to put the worst-rated products first, add product_name ASC as an explicit tie-break so the tied low block is deterministic, project product_name and rating, then LIMIT 10.

The solution

SELECT product_name, rating
FROM products
WHERE rating IS NOT NULL
ORDER BY rating ASC, product_name LIMIT 10;

Common follow-up questions

• How would you return the ten lowest-rated products within each `category` instead of overall? (Tests whether the candidate reaches for `ROW_NUMBER() OVER (PARTITION BY category ORDER BY rating ASC, product_name)` with an outer filter `WHERE rn <= 10`.)
• How would you change the query so only `in_stock = 1` products can appear in the bottom ten? (Tests filter composition. Adding `AND in_stock = 1` to the WHERE clause before `LIMIT` keeps only stocked products in contention. `in_stock` is an INTEGER 0/1 column.)
• What would change if you wanted to discount products with very few reviews from this list? (Tests Bayesian ranking intuition. With `rating` already aggregated, the schema does not expose review counts, so the candidate should call out that this needs either a separate reviews table or a stored `review_count` column to weight low-volume ratings differently.)