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

 -- 求平均用时和平均分数
with er as
(select 
    *,
    avg(timestampdiff(second,start_time,submit_time)) over(partition by exam_id) as avg_time,
    avg(score) over(partition by exam_id) as avg_score
from exam_record)

select 
    er.emp_id,
    emp_level,
    tag as exam_tag
from er 
join emp_info emp on emp.emp_id=er.emp_id
join examination_info e2 on er.exam_id=e2.exam_id
where emp_level<7 
and timestampdiff(second,start_time,submit_time)<avg_time 
and score>avg_score
order by er.emp_id,er.exam_id;