WITH dept_leaders AS (
  SELECT
    e.emp_id,
    e.first_name,
    e.last_name,
    e.dept_id,
    d.dept_name,
    COUNT(DISTINCT pa.project_id) AS project_count,
    RANK() OVER (PARTITION BY e.dept_id ORDER BY COUNT(DISTINCT pa.project_id) DESC) AS rnk
  FROM employees e
  JOIN departments d ON e.dept_id = d.dept_id
  LEFT JOIN project_assignments pa ON e.emp_id = pa.emp_id
  GROUP BY e.emp_id, e.first_name, e.last_name, e.dept_id, d.dept_name
)
SELECT dept_name, first_name, last_name, project_count
FROM dept_leaders
WHERE rnk = 1
ORDER BY project_count DESC;