题解 | 分析每个员工在不同项目中的绩效情况

with t1 as (select e.employee_id as employee_id, employee_name,department,project_id, performance_score from employees e
join performance per
on e.employee_id = per.employee_id),

t2 as (select employee_id, employee_name,department,t1.project_id, performance_score as first_half_2024_score,project_name as project_group,start_date,rank()over(partition by t1.project_id order by performance_score desc,employee_id asc) as project_group_rank from 
(select * from projects
where date(start_date) between '2024-01-01' and '2024-07-01') as pro
join t1 
on t1.project_id = pro.project_id)

select employee_id,employee_name,first_half_2024_score,project_group_rank,department,project_group from t2