CREATE MATERIALIZED VIEW mv_dept_summary AS
SELECT
  d.dept_name,
  COUNT(e.emp_id) AS headcount,
  ROUND(AVG(e.salary), 0) AS avg_salary,
  SUM(e.salary) AS salary_cost,
  COUNT(DISTINCT p.project_id) AS active_projects
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
LEFT JOIN projects p ON d.dept_id = p.dept_id AND p.status = 'active'
GROUP BY d.dept_id, d.dept_name;

CREATE UNIQUE INDEX ON mv_dept_summary(dept_name);

-- Refresh
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dept_summary;

-- Query it like a table
SELECT * FROM mv_dept_summary ORDER BY headcount DESC;