Lowest Average Price Category

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

L3

Asked in

Forbes

Problem

Find the one product category in products with the lowest average price, along with that average. A pricing analyst is hunting for the most budget-friendly corner of the whole catalog, so every product counts, in stock or not.

The cheapest category. Not necessarily the worst.

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 an extremal-group question dressed up as a pricing review: fold 200 products into one mean per category, then keep the category at the bottom. Everyone knows AVG. The candidates who slip read 'cheapest category' as 'the category that owns the cheapest product', so they sort product rows or reach for MIN(price). On this catalog that names Beauty, home of a free sample at 0.0, when the lowest average belongs to Collectibles at about 77.76.

Step 1: Fix the grain at one row per category

The question compares categories, so every product has to be folded into its category before anything is compared. GROUP BY category with AVG(price) gives eleven rows, one mean each. Anything sorted before that fold is sorting products, and answers a different question.

Step 2: Sort on the mean, not on price

ORDER BY avg_price ASC puts the eleven category means lowest first. Sorting on the raw price is what the row-level version does, and it measures the single cheapest item rather than the category as a whole.

Step 3: Keep one row with LIMIT 1

The ask is the single cheapest category, so LIMIT 1 cuts right after the sort. Collectibles sits at 77.76 and every other category averages in the hundreds, so the cut never falls between tied rows and no tie rule is needed.

SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
ORDER BY avg_price ASC
LIMIT 1

Sorting products

SELECT category, price FROM products ORDER BY price LIMIT 1 returns one product's row: the free sample in Beauty at 0.0, or one of the nine unpriced products on an engine that sorts NULL first. It tells you where the cheapest item lives.

Comparing category means

GROUP BY category followed by ORDER BY avg_price compares eleven means and returns Collectibles at 77.76, the category that is cheapest on the whole.

Common follow-up questions

• If two categories ever tied for the lowest average, how would you return both? (Tests replacing `LIMIT 1` with a comparison against the minimum average or a tie-aware rank.)
• Only categories with at least five priced products should qualify. Where does that rule go? (Tests filtering groups after the aggregate rather than filtering rows before it.)
• Pricing now wants the average over in-stock products only. What changes, and can a category disappear? (Tests a row filter on `in_stock` before the fold. Music has no in-stock products at all, so it drops out.)