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

with first_half_year_projects as (
    select
            *
    from 
            projects
    where 
            start_date between '2024-01-01' and '2024-07-01'
)
select
        p.employee_id,
        e.employee_name,
        p.performance_score as first_half_2024_score,
        row_number() over(partition by p.project_id order by p.performance_score desc,p.employee_id ) as project_group_rank,
        e.department,
        fhyp.project_name as project_group
from 
        performance as p
inner join 
        first_half_year_projects as fhyp
on 
        p.project_id = fhyp.project_id
left join 
        employees as e
on 
        p.employee_id = e.employee_id
order by 
        p.project_id,
        project_group_rank,
        p.employee_id