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

select t2.emp_id,a.emp_level,c.tag
from ( 
    select a.emp_id,a.exam_id
    from (
        select  emp_id,exam_id,
                avg(submit_time-start_time) over(partition by exam_id) avg_time,
                avg(score) over(partition by exam_id) avg_score
        from exam_record a
    )t1
    join exam_record a
    on a.emp_id = t1.emp_id
    and a.exam_id= t1.exam_id
    where (submit_time-start_time) < avg_time
    and score > avg_score
    group by emp_id,exam_id
)t2
join emp_info  a
on a.emp_id = t2.emp_id
join examination_info c
on t2.exam_id = c.exam_id
where a.emp_level < 7
order by t2.emp_id,t2.exam_id