题解 | 完成员工考核试卷突出的非领导员工

with t1 as (select  # 计算员工作答时长, 补充试卷类型
                a.emp_id,
                a.exam_id,
                timestampdiff(second,a.start_time,a.submit_time) as duration,
                a.score,
                b.tag
            from exam_record a
            left join examination_info b on a.exam_id = b.exam_id),

t2 as (select  #计算员工平均作答时长和平均分数
            *,
            avg(duration) over(partition by tag) as avg_duration,
            avg(score) over(partition by tag) as avg_score
        from t1)

select # 筛选每类试卷的最佳员工
    t2.emp_id,
    a.emp_level,
    t2.tag as exam_tag
from t2 
left join emp_info a on t2.emp_id = a.emp_id 
where t2.duration < t2.avg_duration and t2.score > t2.avg_score and a.emp_level<7