Solving tips
- AVG ignores NULLs entirely — it divides the sum by the count of non-NULL values, not by the row count.
- Wrap AVG in ROUND(expr, 2) to control the number of decimal places in the output.
- GROUP BY the category, then order by the aggregate alias in ORDER BY.
Given a product catalog, compute the average price within each category.
Schema
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
category TEXT NOT NULL,
price NUMERIC -- NULL means price not yet set
);
Sample data:
| product_id | category | price |
|---|
| 1 | Books | 10 |
| 2 | Books | 20 |
| 3 | Books | 30 |
| 4 | Electronics | 100 |
| 5 | Electronics | 300 |
| 6 | Electronics | NULL |
| 7 | Toys | 50 |
Task
For each category, return:
categoryavg_price — the average of price in that category, rounded to 2 decimal places
Order the result by avg_price descending.
Expected output
| category | avg_price |
|---|
| Electronics | 200.00 |
| Toys | 50.00 |
| Books | 20.00 |
Approach
Group by category and apply AVG(price). AVG sums the non-NULL prices and divides by the count of non-NULL prices, so NULL rows are excluded from both parts of the calculation. ROUND(..., 2) formats each average to two decimals.
Query
SELECT
category,
ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
ORDER BY avg_price DESC;
Walkthrough
- Books (ids 1, 2, 3): (10 + 20 + 30) / 3 = 20.00.
- Electronics (ids 4, 5, 6): id 6 has a NULL price, so it is skipped. The average is (100 + 300) / 2 = 200.00, not 400 / 3. This is the key NULL behavior.
- Toys (id 7): a single row, 50 / 1 = 50.00.
ORDER BY avg_price DESC gives Electronics (200), Toys (50), Books (20).
Complexity & notes
- One aggregate pass over the table, O(n).
- Common trap: assuming NULL prices count as 0 and drag the average down. They do not —
AVG divides by the number of non-NULL values. If you need NULLs treated as 0, use AVG(COALESCE(price, 0)) instead.
ROUND(AVG(price), 2) returns NUMERIC; against NUMERIC input the two-argument ROUND is standard. In some engines you must cast to numeric first, e.g. ROUND(AVG(price)::numeric, 2).- Ordering by the alias
avg_price works in Postgres, MySQL, and SQLite; if a dialect rejects it, repeat the expression in ORDER BY.