题解 | 分析员工在不同项目中的绩效表现以及所属部门的平均绩效情况

-- 部门平均绩效得分
select
    e.employee_id,
    department_name,
    performance_score
from employee_projects e
join
    (select 
        employee_id,
        department_name,
        avg(performance_score) over(partition by department_name)as avg_performance_score
    from employee_projects e
    join department_info d using(employee_id)
    group by 1,2)t
    on e.employee_id=t.employee_id
where performance_score>avg_performance_score
order by 1;