SELECT
  cat.name AS category,
  COALESCE(SUM(CASE WHEN EXTRACT(YEAR FROM o.order_date) = 2023 THEN oi.qty * oi.unit_price END), 0) AS revenue_2023,
  COALESCE(SUM(CASE WHEN EXTRACT(YEAR FROM o.order_date) = 2024 THEN oi.qty * oi.unit_price END), 0) AS revenue_2024,
  ROUND(
    (COALESCE(SUM(CASE WHEN EXTRACT(YEAR FROM o.order_date) = 2024 THEN oi.qty * oi.unit_price END), 0)
    - COALESCE(SUM(CASE WHEN EXTRACT(YEAR FROM o.order_date) = 2023 THEN oi.qty * oi.unit_price END), 0))
    / NULLIF(COALESCE(SUM(CASE WHEN EXTRACT(YEAR FROM o.order_date) = 2023 THEN oi.qty * oi.unit_price END), 0), 0) * 100, 2
  ) AS yoy_growth_pct
FROM categories cat
JOIN products p ON cat.cat_id = p.cat_id
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
GROUP BY cat.cat_id, cat.name
ORDER BY yoy_growth_pct DESC NULLS LAST;