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) AS project_group_rank,
    department,
    project_name as project_group
FROM performance
JOIN employees
USING(employee_id)
JOIN projects
USING(project_id)
WHERE start_date BETWEEN '2024-01-01' AND '2024-06-30'
ORDER BY project_id, project_group_rank , employee_id