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

with t1 as (select e.employee_id as employee_id,performance_score,department_name from employee_projects e 
join department_info d 
on e.employee_id = d.employee_id),
t2 as (
select department_name,avg(performance_score) as avg_performance_score from t1
group by department_name
),
t3 as 
(
select employee_id,performance_score,t1.department_name as department_name,avg_performance_score,
case
when performance_score > avg_performance_score then 1
else 0
end as 1true
 from t1 
join t2 
on t1.department_name = t2.department_name
)
select employee_id,department_name,performance_score from t3
where 1true = 1