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

WITH department_stats AS (
    SELECT 
        ep.employee_id,
        di.department_name,
        ep.performance_score,
        AVG(ep.performance_score) OVER (PARTITION BY di.department_id) as department_avg_score
    FROM employee_projects ep
    JOIN department_info di ON ep.employee_id = di.employee_id
)
SELECT 
    employee_id,
    department_name,
    performance_score
FROM department_stats
WHERE performance_score > department_avg_score
ORDER BY employee_id ASC;