WITH
  -- Salary performance
  sal_rank AS (
    SELECT emp_id,
      RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS sal_rank,
      ROUND(salary / AVG(salary) OVER (PARTITION BY dept_id) * 100, 1) AS sal_vs_dept_avg
    FROM employees
  ),
  -- Project involvement
  proj_stats AS (
    SELECT emp_id,
      COUNT(DISTINCT project_id) AS project_count,
      SUM(hours) AS total_hours
    FROM project_assignments
    GROUP BY emp_id
  ),
  -- Tenure calculation
  tenure AS (
    SELECT emp_id,
      EXTRACT(YEAR FROM AGE(hire_date)) AS years_at_company
    FROM employees
  )
SELECT
  e.first_name || ' ' || e.last_name AS name,
  d.dept_name,
  e.salary,
  sr.sal_rank AS salary_rank_in_dept,
  sr.sal_vs_dept_avg AS salary_vs_dept_avg_pct,
  COALESCE(ps.project_count, 0) AS projects,
  COALESCE(ps.total_hours, 0) AS project_hours,
  t.years_at_company AS tenure_years,
  -- Composite score
  ROUND(
    (100 - sr.sal_rank) * 0.4 +  -- salary rank weight
    COALESCE(ps.project_count, 0) * 5 +  -- project weight
    t.years_at_company * 3  -- tenure weight
  , 0) AS performance_score
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
JOIN sal_rank sr ON e.emp_id = sr.emp_id
LEFT JOIN proj_stats ps ON e.emp_id = ps.emp_id
JOIN tenure t ON e.emp_id = t.emp_id
WHERE e.status = 'active'
ORDER BY performance_score DESC;