Filter Data Using Correlated SQL Subqueries

SELECT
    p.product_name,
    p.price,
    c.category_name
FROM
    Products p
JOIN
    Categories c ON p.category_id = c.category_id
WHERE
    p.price > (
        SELECT AVG(price)
        FROM Products
        WHERE category_id = p.category_id
    )
ORDER BY
    c.category_name, p.price DESC;

How it works: A correlated subquery is a subquery that depends on the outer query for its values and executes once for each row processed by the outer query. This snippet finds products whose price is greater than the average price of all products *within their own category*. The subquery `(SELECT AVG(price) FROM Products WHERE category_id = p.category_id)` is re-evaluated for each product `p` in the outer query, using `p.category_id` to determine the relevant average price for comparison. This allows for dynamic filtering based on category-specific aggregates.