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

with per_temp as (
SELECT e.employee_id,employee_name,performance_score,project_id,department
from employees e
left join performance pe on  pe.employee_id=e.employee_id
),
pro_temp as (
SELECT employee_id,employee_name,performance_score,pr.project_id,project_name,start_date,department,
rank() over(partition by project_name order by performance_score desc) as project_group_rank
from per_temp pt
left join projects pr on pr.project_id=pt.project_id
)
select employee_id,employee_name,performance_score as first_half_2024_score ,project_group_rank,department,
project_name as project_group
from pro_temp 
where start_date between  '2024-01-01' and ' 2024-07-01'
order by project_id,project_group_rank , employee_id