select
    employee_id,
    employee_name,
    performance_score as first_half_2024_score,

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