select 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 employees e join performance p1 using(employee_id) join projects p2 using(project_id) 
where start_date between '2024-01-01' and '2024-07-01'
order by project_id,project_group_rank,employee_id