GROUP BY with Aggregate Functions

SELECT
    p.category_id,
    cat.category_name,
    COUNT(*) AS product_count,
    SUM(oi.quantity) AS total_units_sold,
    SUM(oi.quantity * oi.unit_price) AS total_revenue,
    AVG(oi.unit_price) AS avg_unit_price,
    MIN(oi.unit_price) AS min_price,
    MAX(oi.unit_price) AS max_price
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN categories cat ON p.category_id = cat.category_id
GROUP BY p.category_id, cat.category_name
ORDER BY total_revenue DESC;