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

select emp_id, emp_level, exam_tag from (
select emp_id, emp_level, exam_tag, start_time, submit_time, score
from(
select emp_id, emp_level, tag as exam_tag, start_time, submit_time, score,
    avg(timestampdiff(second, start_time, submit_time)) over(partition by tag) as avg_time,
    avg(score) over(partition by tag) as avg_score
from emp_info em
left join exam_record  ex
    using(emp_id)
left join examination_info  ei
    using(exam_id)
where emp_level<7 
) t
where timestampdiff(second, start_time, submit_time) < avg_time
    and score > avg_score
) m
order by emp_id