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

with
t1 as(
    select
        exam_id,
        avg(timestampdiff(second,start_time,submit_time)/60) as avg_time,
        avg(score) as avg_score
    from
        exam_record
    group by
        exam_id
)
,
t2 as(
    select
        emp_id,
        emp_level,
        exam_id,
        tag as exam_tag,
        score,
        timestampdiff(second,start_time,submit_time)/60 as use_time
    from
        exam_record
        left join emp_info using(emp_id)
        left join examination_info using(exam_id)
    where
        emp_level<7
)
,
t3 as(
    select
        emp_id,
        emp_level,
        exam_tag
    from
        t2 left join t1 using(exam_id)
    where
        use_time<avg_time
        and
        score>avg_score
)


select * from t3