Level 1 · Products priced above average

The starter query fails: AVG cannot appear in WHERE, because WHERE works one row at a time (chapter 4’s quiz). You need the average as a single number first, then compare each row to it. A subquery does exactly that:

SELECT product_id, name, unit_price
FROM products
WHERE unit_price > (SELECT AVG(unit_price) FROM products);

The inner query runs on its own and returns one value. The outer query then uses it like a constant.

Scalar subqueries

A subquery that returns exactly one row and one column is a scalar subquery. It can go anywhere a single value can: WHERE, SELECT, even inside arithmetic:

SELECT
  name,
  unit_price,
  ROUND(unit_price - (SELECT AVG(unit_price) FROM products), 2) AS above_avg_by
FROM products;

If the subquery returns more than one row, the query errors (“more than one row returned by a subquery”). If it returns no rows, the value is NULL, which then makes every comparison unknown and returns nothing. Both are good reasons to run the subquery alone first.

Three places a subquery can live

location	returns	example use
WHERE x > (SELECT ...)	one value	above average
WHERE x IN (SELECT ...)	a list	customers who ordered in 2025
FROM (SELECT ...) AS t	a table	aggregate, then aggregate again

The last one, a derived table, is what you used in chapter 5 to pre-aggregate before a join. Postgres and MySQL require an alias for it (AS t); DuckDB and BigQuery do not, but always add one.

Correlated subqueries

A subquery can reference the outer row. This one compares each product with the average of its own category:

SELECT p.name, p.category, p.unit_price
FROM products AS p
WHERE p.unit_price > (
  SELECT AVG(p2.unit_price)
  FROM products AS p2
  WHERE p2.category = p.category
);

Logically the inner query runs once per outer row. Modern optimizers (DuckDB’s especially) rewrite it into a join, but on some databases correlated subqueries over big tables are slow. Chapter 8 shows the window-function version, AVG(unit_price) OVER (PARTITION BY category), which is usually clearer.

Average of what?

“Average price” here is the average over products: each product counts once. The average price paid across order lines would weight best-sellers more heavily and give a different number. When a ticket says “average”, decide which population it is over and say so.

Common mistakes

• Comparing with a rounded average. Round only what you display; compare with the exact value.
• Using = with a subquery that can return several rows. Use IN instead.