Level 6 · Premium picks in every category

A subquery that stands alone runs once. A correlated subquery refers to a column of the outer query, so its value changes from row to row. That is what “compare with your own group” needs.

Uncorrelated versus correlated

The starter runs the subquery once:

SELECT COUNT(*) AS products
FROM products
WHERE unit_price > (SELECT AVG(unit_price) FROM products);

The store-wide average is $270.16 and 33 products beat it: 16 in Furniture, 10 in Outdoor, 6 in Lighting and 1 in Kitchen. No Decor or Textiles product comes close, because the expensive categories pull the average up.

The correlated version ties the inner query to the current outer row:

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

Read it as “for this product p, average the prices of the products p2 in the same category”. The aliases are not decoration: without them, category = category inside the subquery refers to the inner table on both sides and is always true, so you silently get the global average back.

With the correlation, every category has a premium shelf: 9 Decor products beat the Decor average of $84.79, and 8 Furniture products beat the Furniture average of $740.00.

Performance and alternatives

Logically, a correlated subquery runs once per outer row. Modern engines, DuckDB included, usually rewrite it into a join behind the scenes, so it is fast on tables like this. Two equivalent formulations you will meet: join products to a grouped subquery of category averages, or use a window function, AVG(unit_price) OVER (PARTITION BY category), which comes in the next chapter.

Common mistakes

• Comparing with the rounded average. $84.79 is rounded; compare with the raw AVG and round only for display.
• Writing GROUP BY category in the subquery without correlation. That returns six rows and the comparison fails with “more than one row returned”.

In SQL Server

Correlated subqueries work the same way. If unit_price were an integer column, AVG would return an integer in SQL Server (truncated), so write AVG(CAST(unit_price AS decimal(10,2))) or AVG(unit_price * 1.0).