select
    e.employee_id,
    employee_name,
    performance_score first_half_2024_score,
    row_number() over (partition by project_name order by performance_score desc,e.employee_id) project_group_rank,
    department,
    project_name project_group
from employees e 
    join performance p2 on e.employee_id = p2.employee_id
    join projects p1 on p2.project_id = p1.project_id
where
    start_date between '2024-01-01' and '2024-07-01'
order by p2.project_id,performance_score desc,e.employee_id