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

# 查询每个员工的绩效
# 项目ID，绩效排名，员工ID
with 
t1 as(
    select
        employee_id,
        employee_name,
        performance_score as first_half_2024_score,
        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'
)
,
t2 as(
    select
        employee_id,
        employee_name,
        first_half_2024_score,
        rank()over(partition by project_group order by first_half_2024_score desc,employee_id) as project_group_rank,
        department,
        project_group
    from
        t1
    order by
        project_group,
        project_group_rank,
        employee_id
)

select * from t2