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

with t1 as
(
    select 
    emp_id,
    exam_id,
    score,
    AVG(score) over(partition by exam_id) as avg_sco,
    timestampdiff(second, start_time, submit_time) as all_time
    from 
    exam_record
)
,
t2 as 
(
    select 
    emp_id,
    exam_id,
    score,
    avg_sco,
    all_time,
    AVG(all_time) over(partition by exam_id) as avg_time
    from 
    t1
)
,
t3 as
(
select * from t2 where score > avg_sco and all_time < avg_time
)

select 
emp_info.emp_id,
emp_level,
tag
from 
t3 
left join emp_info on t3.emp_id = emp_info.emp_id
left join examination_info on t3.exam_id = examination_info.exam_id
where emp_level < 7