select a.emp_id,b.emp_level,c.tag as exam_tag
from
(select h.emp_id,h.exam_id
from
(select h2.emp_id,h2.exam_id,
row_number()over(partition by h2.exam_id order by h2.date_diff asc,h2.score desc) as rk
from
(select exam_id,
avg(TIMESTAMPDIFF(second,start_time,submit_time) )as date_diff_avg,
avg(score) as avg_score
from exam_record
group by exam_id

) as h1
left outer join
(
select emp_id,exam_id,
TIMESTAMPDIFF(second,start_time,submit_time) as date_diff,
score 
from exam_record

) as h2 on h1.exam_id=h2.exam_id and h2.date_diff<h1.date_diff_avg and h2.score>h1.avg_score
) as h 
#where h.rk=1
)as a
left outer join
(select emp_id,emp_name,emp_level
from emp_info
) as b on a.emp_id=b.emp_id
left outer join 
(select exam_id,tag
from examination_info
) as c on a.exam_id=c.exam_id
where b.emp_level<7
order by a.emp_id asc