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

with details as(
    select
        er.*,
        avg(score) over (partition by exam_id) as examavgscore,
        avg(timestampdiff(second,start_time,submit_time)) over (partition by exam_id) as avgtime
    from exam_record er
) #建立临时表扩充记录每类考试平均时长与平均分数

select 
    d.emp_id,
    ei.emp_level,
    ei1.tag
from details d 
inner join emp_info ei on d.emp_id=ei.emp_id
inner join examination_info ei1 on d.exam_id=ei1.exam_id
where d.score > examavgscore
and timestampdiff(second,d.start_time,d.submit_time) < avgtime
and ei.emp_level <7
order by d.emp_id