select
    employee_id,
    employee_name,
    performance_score as first_half_2024_score,
    row_number() over (
        partition by
            project_name
        order by
            performance_score desc,
            employee_id asc
    ) as project_group_rank,
    department,
    project_name as project_group
from
    performance
    join projects using (project_id)
    join employees using (employee_id)
where
    start_date between '2024-01-01' and '2024-06-30'
order by
    project_group asc,
    project_group_rank asc,
    employee_id asc;