# Lowest Average Price Category > The cheapest category. Not necessarily the worst. Canonical URL: 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. ## Tables ### products - product_id INTEGER - product_name TEXT - category TEXT - price REAL - rating REAL - in_stock INTEGER Sample rows (10 of 200): | product_id | product_name | category | price | rating | in_stock | | --- | --- | --- | --- | --- | --- | | 1001 | Basic Device 1X | Books | 17.52 | 2.7 | 1 | | 1050 | Deluxe Bundle 2X | Clothing | 25.05 | 4.4 | 1 | | 1099 | Pro Unit 3X | Home & Kitchen | 32.58 | 2.1 | 1 | | 1148 | Ultra Tool 4X | Collectibles | 40.11 | 3.8 | 1 | | 1197 | Essential Set 5X | Toys | 47.64 | 1.5 | 1 | | 1246 | Classic System 6X | Beauty | 0 | 3.2 | 1 | | 1295 | Smart Gadget 7X | Automotive | 62.7 | 4.9 | 0 | | 1344 | Eco Kit 8X | Garden | 70.23 | 2.6 | 1 | | 1393 | Turbo Pack 9X | Music | 77.76 | 4.3 | 0 | | 1442 | Premium Widget 10X | Electronics | 85.29 | 2 | 1 | ## Expected output First 1 of 1 rows: | category | avg_price | | --- | --- | | Collectibles | 77.75999999999999 | ## 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. **The lowest category mean** ```sql SELECT category, AVG(price) AS avg_price FROM products GROUP BY category ORDER BY avg_price ASC LIMIT 1 ``` *One fold to `category`, one sort on `avg_price`, one row kept.* **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. > **`MIN(price)` is the same mistake with a grouping on top** > > Grouping does not save you if the aggregate is wrong. A per-category `MIN(price)` puts Beauty first at 0.0 because one free item drags its minimum to zero, even though Beauty's average sits in the hundreds. > **`AVG` already leaves the unpriced products out** > > Nine products have a `NULL` `price`. `AVG(price)` divides by the count of priced rows, so those products drop out of the mean instead of pulling it toward zero. Writing `COALESCE(price, 0)` would invent nine free items and shift several categories' averages. > **Two products can win a catalog-wide average** > > Collectibles holds only two products, both cheap, and that is exactly why it wins. In a real pricing review the next question is whether a category that thin should qualify at all. That is a minimum-count rule applied after the average, not a change to how the average is computed. > **Say the grain before you type** > > Saying 'one row per category, then the lowest one' before writing anything tells the interviewer you saw the fold. It is also the sentence that stops you from sorting products. > **One scan, eleven groups** > > The plan is a single pass over `products` that keeps a running sum and count for each of eleven categories, then a top-1 over eleven rows. There is no sort of the 200 products and no second scan. ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/lowest_average_price_category) - [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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.