# The Merit Circle > Ten seats at the top, and the ratings decide who sits. Canonical URL: 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. ## Tables ### products - product_id INTEGER - product_name TEXT - category TEXT - price REAL - rating REAL - in_stock INTEGER Sample rows (10 of 200): | product_id | product_name | category | price | rating | in_stock | | --- | --- | --- | --- | --- | --- | | 1001 | Basic Device 1X | Books | 17.52 | 2.7 | 1 | | 1050 | Deluxe Bundle 2X | Clothing | 25.05 | 4.4 | 1 | | 1099 | Pro Unit 3X | Home & Kitchen | 32.58 | 2.1 | 1 | | 1148 | Ultra Tool 4X | Collectibles | 40.11 | 3.8 | 1 | | 1197 | Essential Set 5X | Toys | 47.64 | 1.5 | 1 | | 1246 | Classic System 6X | Beauty | 0 | 3.2 | 1 | | 1295 | Smart Gadget 7X | Automotive | 62.7 | 4.9 | 0 | | 1344 | Eco Kit 8X | Garden | 70.23 | 2.6 | 1 | | 1393 | Turbo Pack 9X | Music | 77.76 | 4.3 | 0 | | 1442 | Premium Widget 10X | Electronics | 85.29 | 2 | 1 | ## Expected output First 5 of 10 rows: | product_name | rating | | --- | --- | | Deluxe Widget v21 | 1 | | Deluxe Widget v61 | 1 | | Pro Unit 33X | 1.1 | | Pro Unit 73X | 1.1 | | Classic System 66X | 1.2 | ## 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 **Filter, sort ascending with tie-break, limit** ```sql SELECT product_name, rating FROM products WHERE rating IS NOT NULL ORDER BY rating ASC, product_name LIMIT 10; ``` > **Cost Analysis** > > Across 15,000 rows the planner does a single sequential scan, applies the NULL filter (drops about 750 rows), then a top-K sort that keeps only the running 10 lowest in memory. An index on `(rating ASC, product_name ASC)` would let the planner walk the index and stop after the first 10 non-NULL rows, but at this row count the scan is already sub-millisecond. > **Interviewers Watch For** > > The make-or-break tells: did you over-engineer with `AVG`/`GROUP BY` when `rating` is already an aggregate, did you add the alphabetical tie-break on `product_name`, and did you handle NULL with `IS NOT NULL` rather than `rating != NULL` (which always returns UNKNOWN and silently drops every row). > **Common Pitfall** > > Forgetting that ascending order sends NULLs to the top. With `ORDER BY rating ASC` and no `WHERE rating IS NOT NULL`, the ~750 unrated rows fill the first ten slots and you never see a real low rating. Pair that with a missing tie-break and both the rows and their order fail the deterministic comparison the grader expects. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_merit_circle) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.