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

with t as (select e.emp_id, e.exam_id, 
timestampdiff(second, e.start_time, e.submit_time) as time, 
e.score, i.tag from exam_record e left join examination_info i 
on e.exam_id = i.exam_id),

t2 as (select *, avg(score) over (partition by tag) as average_score,
avg(time) over (partition by tag) as average_time
from t
),

t3 as (select emp_id, tag from t2 
where score > average_score and time < average_time)

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