Subqueries & CTEs · Medium
Find products priced above the average price of their own category. What is a correlated subquery?
Short answer
A correlated subquery references a column from the outer query, so it is conceptually re-evaluated for every outer row. Here the subquery computes the average price for the current product's category, and the outer WHERE keeps products above it. The same result can be produced with a window function or a join to a pre-aggregated CTE, which often reads better.
Detailed answer
-- Correlated subquery
SELECT p.product_name, p.category, p.price
FROM products p
WHERE p.price > (
SELECT AVG(p2.price)
FROM products p2
WHERE p2.category = p.category -- correlation to the outer row
)
ORDER BY p.category, p.price DESC;
-- Same answer with a window function
SELECT product_name, category, price, ROUND(cat_avg) AS category_avg
FROM (
SELECT product_name, category, price,
AVG(price) OVER (PARTITION BY category) AS cat_avg
FROM products
) t
WHERE price > cat_avg
ORDER BY category, price DESC;
Which to choose? Modern optimisers often turn correlated subqueries into joins, so performance is usually similar on small tables. The window version has a bonus: it can show the category average next to each row, which makes the answer easier to check. Mention both and explain the trade-off — that is what the interviewer is listening for.