select performance.employee_id
, employee_name
, performance_score first_half_2024_score
, row_number() over(partition by project_name order by performance_score desc,performance.employee_id)
project_group_rank
, department
, project_name project_group
from performance
left join projects on performance.project_id = projects.project_id
left join employees on performance.employee_id  = employees.employee_id 
where start_date between '2024-01-01' and '2024-06-30'
order by 6 ,3 desc,1