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

with
    e as (
        select
            *,
            avg(timestampdiff (second, start_time, submit_time)) over (
                partition by
                    exam_id
            ) as time_avg,
            avg(score) over (
                partition by
                    exam_id
            ) as score_avg
        from
            exam_record
    )
select
    e.emp_id,
    a.emp_level,
    b.tag as exam_tag
from
    e e
    left join examination_info b on b.exam_id = e.exam_id
    left join emp_info a on a.emp_id = e.emp_id
where
    e.time_avg > timestampdiff (second, e.start_time, e.submit_time)
    and e.score > e.score_avg
    and a.emp_level < 7