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

with avg_s as (
select tag, 
avg(timestampdiff(second, start_time, submit_time)) as avg_time,
avg(score) as avg_acore
from exam_record
join examination_info using(exam_id)
join emp_info using(emp_id)
group by tag
)

select emp_id, emp_level, tag as exam_tag
from exam_record
join examination_info using(exam_id)
join emp_info using(emp_id)
join avg_s using(tag)
where emp_level < 7
and score > avg_acore
and timestampdiff(second, start_time, submit_time) < avg_time
order by emp_id, exam_id

感觉我的方法挺简洁的