题解 | #SQL类别高难度试卷得分的截断平均值#

select 
    tag,
    difficulty,
    round(avg(score), 1) as clip_avg_score
from 
    (select 
        t2.tag as tag,
        t2.difficulty as difficulty,
        t1.exam_id as exam_id,
        t1.score as score,
        rank() over(order by t1.score) as rk1,
        rank() over(order by t1.score desc) as rk2
    from exam_record as t1
    inner join examination_info as t2
    on t1.exam_id=t2.exam_id
    where t2.tag='SQL' and t2.difficulty='hard' and t1.score is not null) as t
where rk1 <> 1 and rk2 <> 1
group by tag