select t1.employee_id as employee_id, employee_name, performance_score as first_half_2024_score, rank() over (partition by project_name order by performance_score desc) as project_group_rank, department, project_name as project_group
from employees as t1 inner join performance as t2
on t1.employee_id=t2.employee_id
inner join projects as t3
on t2.project_id=t3.project_id
where start_date between '2024-01-01' and '2024-07-01'
order by project_group, project_group_rank, t1.employee_id