Perform Conditional Aggregation Using CASE Statements

SELECT
  product_category,
  COUNT(CASE WHEN order_status = 'completed' THEN 1 END) AS completed_orders,
  COUNT(CASE WHEN order_status = 'pending' THEN 1 END) AS pending_orders,
  SUM(CASE WHEN order_status = 'completed' THEN order_total ELSE 0 END) AS total_revenue
FROM
  orders
GROUP BY
  product_category;

How it works: This query demonstrates conditional aggregation using `CASE` statements within aggregate functions. For each `product_category`, it counts the number of 'completed' and 'pending' orders, and calculates the total revenue only for 'completed' orders. The `CASE` statement evaluates a condition for each row, and if true, a value (like 1 for counting or `order_total` for summing) is passed to the aggregate function; otherwise, it's ignored or a default value (like 0) is used.