WITH
dept_stats AS (
  SELECT
    dept_id,
    COUNT(*) AS headcount,
    AVG(salary) AS avg_salary,
    SUM(salary) AS salary_cost
  FROM employees
  GROUP BY dept_id
),
project_stats AS (
  SELECT
    dept_id,
    COUNT(*) AS project_count,
    SUM(budget) AS project_budget
  FROM projects
  GROUP BY dept_id
)
SELECT
  d.dept_name,
  ds.headcount,
  ROUND(ds.avg_salary, 0) AS avg_salary,
  COALESCE(ps.project_count, 0) AS projects,
  COALESCE(ps.project_budget, 0) AS project_budget
FROM departments d
LEFT JOIN dept_stats ds ON d.dept_id = ds.dept_id
LEFT JOIN project_stats ps ON d.dept_id = ps.dept_id
ORDER BY ds.headcount DESC NULLS LAST;