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

with avg_total as (
    select exam_id,
           avg(score) as avg_score,
           avg(timestampdiff(second, start_time, submit_time)) as avg_time
    from exam_record
    group by exam_id
),
emp_target as (
    select emp_id, exam_record.exam_id
    from avg_total
         join exam_record on avg_total.exam_id = exam_record.exam_id
    where timestampdiff(second, start_time, submit_time) < avg_time
          and score > avg_score
)
select emp_target.emp_id, emp_level, examination_info.tag as exam_tag
from emp_target
     left join emp_info on emp_target.emp_id = emp_info.emp_id
     left join examination_info on emp_target.exam_id = examination_info.exam_id
where emp_level < 7
order by emp_target.emp_id asc, emp_target.exam_id asc

1. 使用开窗函数，求出每种试卷类型的平均分与平均用时，构建avg_total表
2. 从avg_total与exam_record中取出高于平均分且用时小于平均用时的用户与试卷类型，此时得到突出员工，接下来按照题意，对员工进行非领导筛选与排序即可