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

-- 计算出按照试卷类型算出平均分数
with t1 as (
    select
        emp_id,
        exam_id,
        score,
        avg(score) over(partition by exam_id) as avg_score,
        timestampdiff(second , start_time , submit_time) as all_time
        from exam_record   
),
-- 计算出平均用时
t2 as (
    select
        emp_id,
        exam_id,
        score,
        avg_score,
        all_time,
        avg(all_time) over(partition by exam_id) as avg_time
        from t1
),
-- 筛选
t3 as (
    select
        *
        from t2
        where score > avg_score and all_time < avg_time

)
-- 终极筛选
select
emp_info.emp_id,
emp_level,
tag
from
t3
left join emp_info on t3.emp_id = emp_info.emp_id
left join examination_info on t3.exam_id = examination_info.exam_id
where emp_level < 7