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

#计算每张试卷的平均得分和用时，并连接试卷名称
with avg_tb as (
    select
    t1.exam_id,
    t2.tag,
    avg(t1.score) as avg_score,
    avg(timestampdiff(second,t1.start_time,t1.submit_time)) as avg_time
    from exam_record t1
    join examination_info t2 on t1.exam_id = t2.exam_id
    group by exam_id
),
#计算每个员工每张试卷的得分和用时，并连接员工等级
emp_exam_info as (
    select
    t3.emp_id,
    t3.exam_id,
    t4.emp_level,
    max(timestampdiff(second,t3.start_time,t3.submit_time)) as exam_time,
    max(t3.score) as score
    from exam_record t3
    join emp_info t4 on t3.emp_id = t4.emp_id
    group by 1,2,3 
)

select
a.emp_id,
a.emp_level,
b.tag as exam_tag
from emp_exam_info a
left join avg_tb b on a.exam_id = b.exam_id
where a.exam_time < b.avg_time  and a.score > b.avg_score and emp_level < 7
order by a.emp_id, a.exam_id