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

with
-- exam_record为主表，join余下两张表，得出用户作答时长
    t1 as (
        select
            e.emp_id,
            e.exam_id,
            emp_level,
            tag,
            timestampdiff (second, start_time, submit_time) len,
            score
        from
            exam_record e
            left join emp_info ei on e.emp_id = ei.emp_id
            left join examination_info eif on e.exam_id = eif.exam_id
    ),
-- 算出同类试卷的 平均作答时长 和 平均分数
    t2 as (
        select
            exam_id,
            avg(len) avg_time,
            avg(score) avg_score
        from
            t1
        group by
            exam_id
    )
-- t1 和t2表联结，按条件过滤出答案
select
    emp_id,
    emp_level,
    tag as exam_tag
from
    t1
    left join t2 on t1.exam_id = t2.exam_id
where
    len < avg_time
    and score > avg_score
    and emp_level < 7