Find Categories with Products Above a Certain Average Price

SELECT
    p.category_id,
    c.category_name,
    COUNT(p.product_id) AS product_count,
    AVG(p.price) AS average_price
FROM
    products p
JOIN
    categories c ON p.category_id = c.category_id
GROUP BY
    p.category_id, c.category_name
HAVING
    AVG(p.price) > 50.00
ORDER BY
    average_price DESC;

How it works: This snippet demonstrates how to use `GROUP BY` with an aggregate function (`AVG`) and `HAVING` to filter groups. It calculates the average price for products within each category and then uses `HAVING` to show only those categories where the average price exceeds 50.00. This is invaluable for reporting and analytical queries to understand data trends at a group level.