select 
    e.employee_id,
    e.employee_name,
    coalesce(p.performance_score,0) as first_half_2024_score,
    dense_rank() over(partition by j.project_id order by p.performance_score desc) as project_group_rank,
    e.department,
    j.project_name as project_group
from 
    employees e
left join 
    performance p on e.employee_id=p.employee_id
left join 
    projects j on p.project_id=j.project_id
where 
    j.start_date between '2024-01-01' and '2024-06-30'
order by 
    j.project_id,project_group_rank,e.employee_id