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

with tiaojian as (
select 
ed.emp_id,
eo.exam_id,
emp_level,
eo.tag,
ed.score,
timestampdiff(minute,start_time,submit_time) as pt,
avg(score)over(partition by eo.exam_id) as pc,
avg(timestampdiff(minute,start_time,submit_time))over(partition by eo.exam_id) as pdiff
from exam_record ed inner join emp_info ef 
on ed.emp_id=ef.emp_id
inner join examination_info eo 
on eo.exam_id=ed.exam_id
)


select 
emp_id,
emp_level,
tag
from tiaojian 
where
pt<pdiff
and 
score>pc
and
emp_level<7
order by emp_id asc,exam_id asc