Correlated Subqueries

A correlated subquery references columns from the outer query. It executes once for each row in the outer query.

SELECT p.name, p.price,
    (SELECT AVG(price) FROM products WHERE category = p.category) as category_avg
FROM products p;

This calculates each product's category average dynamically.

Your Task

For each product, show:

• Product name
• Product price
• The maximum price in that product's category (as category_max)

Limit to 6 results ordered by category, then by price descending.