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

WITH t AS (
    SELECT 
        emp_id,  
        score,
        tag, 
        ei.exam_id, 
        -- 计算考试用时（分钟）：提交时间-开始时间
        ABS(TIMESTAMPDIFF(MINUTE, submit_time, start_time)) AS use_duration, 
        -- 同标签考试的平均用时
        AVG(ABS(TIMESTAMPDIFF(MINUTE, submit_time, start_time))) OVER(PARTITION BY tag) AS use_duration_avg,  
        -- 同标签考试的平均分数
        AVG(score) OVER(PARTITION BY tag) AS score_avg 
    FROM exam_record er 
    JOIN examination_info ei ON er.exam_id = ei.exam_id
) 
SELECT 
    e.emp_id,
    e.emp_level, 
    t.tag AS exam_tag  
FROM emp_info e 
JOIN t ON e.emp_id = t.emp_id 
WHERE use_duration < use_duration_avg  
  AND score > score_avg 
  AND emp_level < 7  
ORDER BY e.emp_id, t.exam_id;