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

with t1 as(
select 
    t1.emp_id,
    exam_id,
    emp_level,
    submit_time-start_time as answer_time,
    score
from emp_info t1 inner join exam_record t2 on t1.emp_id = t2.emp_id 
where emp_level < 7
),
t2 as(
select 
    exam_id,
    avg(submit_time-start_time) as avg_answer_time,
    avg(score) as avg_score
from exam_record 
group by exam_id
),t3 as(
select 
    emp_id,
    t1.exam_id,
    emp_level,
    answer_time,
    score,
    avg_answer_time,
    avg_score 
from t1 left join t2 on t1.exam_id=t2.exam_id
),
t4 as(
select 
    emp_id,
    exam_id ,
    emp_level
from t3
where answer_time < avg_answer_time and score > avg_score
)
select 
    emp_id,
    emp_level,
    tag as exam_tag
from t4 inner join examination_info on t4.exam_id = examination_info.exam_id