select 
p.employee_id employee_id,
department_name department_name,
performance_score
from employee_projects p 
join (
    select avg(performance_score) over (partition by project_id) avg_ps,
    employee_id
    from employee_projects
) t on p.employee_id=t.employee_id
join department_info f on p.employee_id=f.employee_id
where performance_score>avg_ps
order by employee_id

对于对一列进行group by却对另一列进行计算输出时，可以考虑使用窗口函数代替groupby