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

select t.emp_id , t.emp_level , t.exam_tag
from(select em.emp_id , 
            em.emp_level , 
            ei.tag exam_tag ,
            er.score , 
            timestampdiff(minute , start_time , submit_time) answer_time ,  
            avg(timestampdiff(minute , start_time , submit_time)) over(partition by ei.exam_id)avg_time , 
            avg(er.score) over(partition by ei.exam_id)avg_score
     from emp_info em join exam_record er on em.emp_id = er.emp_id
            join examination_info ei on er.exam_id = ei.exam_id
     where em.emp_level < 7) t
where t.score > t.avg_score and t.answer_time < t.avg_time
order by t.emp_id;