SELECT E.employee_id,
       employee_name,
       performance_score AS first_half_2024_score,
       row_number()over(partition by project_name order by performance_score DESC) AS project_group_rank,
       department,
       project_name AS project_group
FROM performance AS PER
JOIN employees AS E ON PER.employee_id = E.employee_id
JOIN projects AS P ON P.project_id = PER.project_id
WHERE start_date BETWEEN '2024-01-01' AND '2024-06-30'
ORDER BY P.project_id,project_group_rank,E.employee_id;