Price Check

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

Problem

Which categories in products are the most expensive on average, and how many products in each actually have a price on file? A pricing analyst wants every category listed with both figures, most expensive first.

Priced to sell or priced to sit?

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 NULL-semantics question dressed up as a pricing report. AVG(price) already skips the 9 rows of products that have no price, so the average comes out right whether you filter or not, and that is exactly what lulls people. COUNT(*) skips nothing: it counts rows. Leave the filter off and Garden reports 20 products where only 19 have a price, Music 20 where only 18 do, while every average still matches the key, so the bug sails through review.

Walking to the answer

Step 1: Decide who is in the population

Nine rows have a NULL price, spread over Music, Beauty, Toys, Garden, Automotive and Clothing. The question counts products with a price on file, so those nine belong in neither figure. Drop them once with WHERE price IS NOT NULL, before grouping, and both metrics describe the same set of products.

Step 2: Collapse to one row per category

GROUP BY category yields eleven rows, including Collectibles with only two products. Nothing asks you to hide small categories, so there is no group filter to write.

Step 3: Compute the two figures

AVG(price) becomes avg_price and COUNT(*) becomes product_count. With the WHERE in place, COUNT(*) and COUNT(price) agree; without it, only COUNT(price) would still be right.

Step 4: Sort by the average, highest first

ORDER BY avg_price DESC reuses the alias instead of repeating AVG(price). Sports leads at about 972 and Collectibles trails at about 78.

SELECT
    category,
    AVG(price) AS avg_price,
    COUNT(*) AS product_count
FROM products
WHERE price IS NOT NULL
GROUP BY category
ORDER BY avg_price DESC

Count rows

No filter, COUNT(*): Garden, Music, Beauty, Toys, Automotive and Clothing each report 20. The avg_price column is identical to the correct answer, which is why nobody notices.

Count priced products

WHERE price IS NOT NULL then COUNT(*) (or COUNT(price) with no filter): Garden 19, Automotive 19, Clothing 19, Music 18, Beauty 18, Toys 18.

Common follow-up questions

• How would you also report how many products in each category are missing a price? (Tests counting the excluded rows per group in the same pass instead of a second query.)
• If the analyst wants `avg_price` rounded to cents, where does the rounding go, and can it change the sort? (Tests rounding for display versus sorting on full precision.)
• What changes if `category` itself can be `NULL`? (Tests knowing that all `NULL` categories fall into one group of their own.)