SELECT
  e.first_name || ' ' || e.last_name AS employee,
  e.job_title,
  COUNT(DISTINCT pa.project_id) AS projects_count,
  SUM(pa.hours) AS total_hours,
  MAX(pa.assigned_on) AS latest_assignment
FROM employees e
LEFT JOIN project_assignments pa ON e.emp_id = pa.emp_id
GROUP BY e.emp_id, e.first_name, e.last_name, e.job_title
ORDER BY total_hours DESC NULLS LAST;