Rating Tiers

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

L4

Problem

The review team sorts each category's products into whole-star tiers, where a product's tier is its rating rounded down to a whole number, so a 4.9 and a 4.2 both count as 4 stars. Within each category, number those tiers from the highest downward, so products in the same tier share a number and the next tier down takes the very next one with no gaps. Skip products with no rating, and return the product_name, category, rating, and that number as position.

No gaps, no skips. Ratings stacked tight within each category.

Tables

products

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

Expected output

Worked solution and explanation

The real question hiding in 'tier by rating'

Strip the business words and this is a two-step transform wearing a ranking costume. First you collapse a continuous rating into a discrete whole-star bucket, which is what CAST(rating AS INTEGER) does: 4.9 and 4.2 both become the 4-star tier. Only then do you rank those tiers per category. Anyone can reach for a window function; the tell is whether they rank the raw rating (splitting every star tier into a dozen positions) or the cast tier. Get the cast wrong and the position column is wrong for nearly every row. Reach for RANK instead of DENSE_RANK and every position after a tie inflates: 1, 1, 3 where the spec wants 1, 1, 2.

Working the requirements

Step 1: Bucket rating into a whole-star tier

The rating column is a REAL like 4.9 or 4.2. A whole-star tier is that value with the fraction dropped, which in SQL is CAST(rating AS INTEGER) (it truncates toward zero, and every rating is positive, so it floors). Skip this and you rank the raw rating, so 4.9, 4.8 and 4.2 each land in a separate position instead of sharing the 4-star tier.

Step 2: Rank the tiers per category with DENSE_RANK

DENSE_RANK() OVER (PARTITION BY category ORDER BY CAST(rating AS INTEGER) DESC). PARTITION BY category restarts the count at 1 for each category. ORDER BY ... DESC puts the highest star tier at position 1. DENSE_RANK is the one of the three ranking functions that lets tied tiers share a position AND keeps the next tier contiguous (1, 1, 2), which is exactly the 'no gaps' contract. RANK would give 1, 1, 3; ROW_NUMBER would give 1, 2, 3 and refuse to let a tier share.

Step 3: Drop products with no rating

WHERE rating IS NOT NULL drops the roughly 5% of products with no rating. Filter before the window, not after: a NULL rating left in would cast to NULL, sort to the bottom, and either pick up a spurious position or pad the result with unrated rows. The spec says skip them entirely.

The solution

SELECT
    product_name,
    category,
    rating,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY CAST(rating AS INTEGER) DESC) AS position
FROM products
WHERE rating IS NOT NULL

Common follow-up questions

• What if the team wanted half-star tiers (4.5, 4.0, 3.5) instead of whole stars? (multiply by 2, cast to integer, then divide back, or round to the nearest 0.5; shows they understand the cast is just a bucketing step.)
• How would you return only the top 2 star tiers per category? (wrap in a subquery and filter `WHERE position <= 2`; note that ties mean the row count per category can exceed 2.)
• What changes if `rating` were stored as TEXT like '4.9'? (now the CAST does double duty: without it, ORDER BY sorts lexically so '9.0' < '10.0' is false; the number one silent bug in production SQL.)