SELECT
  c.name AS category,
  COUNT(DISTINCT p.product_id) AS product_count,
  SUM(oi.qty) AS total_units_sold,
  SUM(oi.qty * oi.unit_price) AS total_revenue
FROM categories c
LEFT JOIN products p ON c.cat_id = p.cat_id
LEFT JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY c.cat_id, c.name
ORDER BY total_revenue DESC NULLS LAST;