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

with einfo as (
    select 
    emp_id,exam_id,
    timestampdiff(second,start_time,submit_time)*1.0/3600 etime,
    score,
    avg(timestampdiff(second,start_time,submit_time)*1.0/3600) over(partition by exam_id) avg_etime,
    avg(score) over(partition by exam_id) avg_score
    from exam_record
)
select ei.emp_id,em.emp_level,ex.tag as exam_tag
from einfo as ei
left join emp_info as em on ei.emp_id=em.emp_id
left join examination_info as ex on ei.exam_id=ex.exam_id
where em.emp_level<7 and ei.etime<avg_etime and ei.score>avg_score
order by ei.emp_id,ex.exam_id