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

select
    a.emp_id as emp_id,
    emp_level,
    tag as exam_tag
from
    emp_info a,
    examination_info b,
    (
        select
            *,
            avg(timestampdiff (second, start_time, submit_time)) over (
                partition by
                    exam_id
            ) as avg_time,
            avg(score) over (
                partition by
                    exam_id
            ) as avg_score
        from
            exam_record
    ) c
where
    a.emp_id = c.emp_id
    and b.exam_id = c.exam_id
    and timestampdiff (second, start_time, submit_time) < avg_time
    and score > avg_score
    and emp_level < 7
group by
    b.exam_id,
    tag,
    a.emp_id
order by
    a.emp_id,
    b.exam_id