SQL: list all products with a price higher than the average price in their category (table products with fields product_id, category, price).

To display products with a price higher than the average price in their category, you can use a window function or a subquery to calculate the average price per category.

Example with a subquery:

SELECT product_id, category, price
FROM products p
WHERE price > (
    SELECT AVG(price)
    FROM products
    WHERE category = p.category
);

This query compares each product's price with the average price of its category and displays only those products with a price above the average.