# Rating Tiers > No gaps, no skips. Ratings stacked tight within each category. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem The review team wants product ratings tiered inside each category with no gaps on ties. Within each category, rank products by rating with the highest rating at position 1; tied ratings share a position, and the next rating down takes the immediately following position. Skip products with no rating. Return the product_name, category, rating, and position. ## Worked solution and explanation ### Why this problem exists in real interviews Ranking within a group with no gaps is the window-function interview question. Every analytics platform needs it: rank products within category, rank employees within department, rank customers within segment. Interviewers use this prompt to separate candidates who know the three ranking functions (ROW_NUMBER, RANK, DENSE_RANK) from those who only know ORDER BY. The twist here is 'no gaps after ties,' which is the exact definition of DENSE_RANK. --- ### Break down the requirements #### Step 1: Choose DENSE_RANK for gap-free ties ROW_NUMBER breaks ties arbitrarily (1, 2, 3 even for equal values). RANK leaves gaps after ties (1, 1, 3). DENSE_RANK leaves no gaps after ties (1, 1, 2). The spec says 'products that share a rating share the position, and the next rating down takes the immediately following position.' That is DENSE_RANK, verbatim. #### Step 2: Partition by category, order by rating DESC PARTITION BY category restarts the rank count at 1 for each category. ORDER BY rating DESC puts the highest rating at position 1. Cast to DOUBLE if the column is stored as text or numeric to ensure numeric ordering, not lexicographic (where '9' > '10'). #### Step 3: Filter out products with no rating WHERE rating IS NOT NULL removes unrated products. DENSE_RANK would otherwise rank them (NULLs sort last by default in most engines), and the spec says to skip them entirely. Filter before the window, not after: a subquery with HAVING would not remove them from the ranking. --- ### The solution **DENSE_RANK partitioned by category** ```sql SELECT product_name, category, rating, DENSE_RANK() OVER (PARTITION BY category ORDER BY CAST(rating AS DOUBLE) DESC) AS position FROM products WHERE rating IS NOT NULL ``` > **Cost Analysis** > > Window functions sort within each partition, so cost is O(n log n) worst case, dominated by the partition sort. With an index on (category, rating DESC), the engine can stream the sorted rows and compute ranks in a single pass, dropping to O(n). Modern engines (Postgres, Snowflake, BigQuery) handle this automatically if the stats favor it. > **Interviewers Watch For** > > Whether you name all three ranking functions and pick the right one, whether you PARTITION by the right column (category, not product_name), and whether you handle NULLs per the spec. Strong candidates mention the CAST to DOUBLE for string-typed rating columns, which is exactly the kind of silent-bug trap SQLite and MySQL expose with implicit type coercion. > **Common Pitfall** > > Using RANK instead of DENSE_RANK and producing gaps (1, 1, 3, 4) when the spec says (1, 1, 2, 3). Another classic: forgetting PARTITION BY and getting a global rank across all categories. And the silent killer: rating stored as TEXT and ordering lexicographically, so '4.5' < '4.55' is true but '9.0' < '10.0' is false (because '9' > '1' character-wise). The CAST guards against this. --- ## Common follow-up questions - What if the spec wanted gaps after ties (RANK behavior, 1, 1, 3)? _(swap DENSE_RANK for RANK. Explain the difference with a concrete tie example.)_ - How would you keep only the top 3 products per category? _(wrap in a subquery and filter WHERE position <= 3; note that with DENSE_RANK, ties at position 3 all pass, so the count can exceed 3.)_ - What changes if the rating column contains mixed types (strings, numbers, NULLs)? _(discuss TRY_CAST in BigQuery/Snowflake, or a CASE expression that guards the CAST. Mention that ORDER BY on TEXT with numeric content is the #1 silent bug in production SQL.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/rating_tiers) - [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.