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

with
    temp1 as (
        select
            avg(score) over (
                partition by
                    exam_id
            ) as avg_score,
            avg(timestampdiff(minute, start_time, submit_time)) over (
                partition by
                    exam_id
            ) as avg_time,
            timestampdiff(minute, start_time, submit_time) as time,
            emp_id,
            score,
            tag,
            e.exam_id
        from
            exam_record e
            join examination_info ei on ei.exam_id = e.exam_id
    )
select
    t.emp_id,
    emp_level,
    tag as exam_tag
from
    temp1 t
    join emp_info emp on emp.emp_id = t.emp_id
where
    time < avg_time
    and score > avg_score
    and emp_level < 7
order by
    t.emp_id,
    exam_id;