Profitable Categories by Price

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

L4

Asked in

Forbes

Problem

The products table holds one row per catalog item with its category, price, rating and an in_stock flag, and an out-of-stock item counts toward nothing. Report the lowest in-stock price and the average in-stock rating for every category whose in-stock items average a rating of at least 3.0.

The most profitable categories.

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

This is a two-grain filtering problem dressed up as a catalog report. There are two conditions, and they look alike because both sound like 'which products count'. They are not alike. The stock rule is about a single row: is this item on the shelf? The rating bar is about a whole category: do its in-stock items average 3.0 or better? Anyone can write the GROUP BY. What separates candidates is putting each condition at its own grain. Put the rating bar on the rows and every category clears it, every average is inflated, and the cheapest low-rated item vanishes from the minimum price.

Build it in order

Step 1: Decide which rows exist

WHERE in_stock = 1 runs before any grouping. Out-of-stock items are not part of this report at all, so they must not feed the average or the minimum. Skip it and both measures drift, which can move a category across the 3.0 line in either direction.

Step 2: Collapse to one row per category

GROUP BY category with MIN(price) and AVG(rating) computes both measures over the same eligible row set. Both aggregates skip NULLs, so the few unrated or unpriced items simply do not vote, which is what you want.

Step 3: Judge each category by its average

HAVING AVG(rating) >= 3.0 runs after the aggregation, on the category's number. This is the only place that condition can live: a WHERE never sees an average, only one row's rating. On this catalog the bar is real: some categories fall short of it and drop out.

SELECT
    category,
    MIN(price) AS min_price,
    AVG(rating) AS avg_rating
FROM products
WHERE in_stock = 1
GROUP BY category
HAVING AVG(rating) >= 3.0

Rating bar on rows

WHERE in_stock = 1 AND rating >= 3.0 throws away every low-rated item before averaging. Each avg_rating is computed over items already at 3.0 or better, so no category can fail, and min_price ignores cheap items with weak ratings.

Rating bar on the group

WHERE in_stock = 1 then HAVING AVG(rating) >= 3.0 averages every in-stock item, good and bad, and only then asks whether the category as a whole clears the bar. Weak categories drop out; strong ones report honest numbers.

Common follow-up questions

• How would you also report how many in-stock items each surviving category has? (Adding a `COUNT(*)` alongside the existing aggregates without disturbing the filters.)
• What if a category's in-stock items all have a NULL `rating`? (Whether `AVG` returns NULL and how a NULL compares against the 3.0 bar in `HAVING`.)
• What changes if the bar should apply to all items while the price comes only from in-stock ones? (Moving the stock rule from `WHERE` into a conditional aggregate such as `MIN(CASE WHEN in_stock = 1 THEN price END)`.)