Create and Refresh Materialized Views

-- Create a materialized view
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
  DATE_TRUNC('month', order_date) AS month,
  product_category,
  SUM(total) AS revenue,
  COUNT(*) AS order_count
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY 1, 2;

-- Create index on the materialized view
CREATE INDEX idx_mv_month ON monthly_revenue (month);

-- Refresh the materialized view
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

-- Query it like a regular table
SELECT * FROM monthly_revenue
WHERE month >= '2025-01-01'
ORDER BY revenue DESC;