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

select
    tag tag,
    difficulty difficulty,
    round(avg(score), 1) clip_avg_score
from
    (
        select
            tag,
            difficulty,
            score,
            row_number() over (
                order by
                    score asc
            ) as rk_asc,
            row_number() over (
                order by
                    score desc
            ) as rk_desc
        from
            exam_record er
            join examination_info ei on ei.exam_id = er.exam_id
        where
            ei.tag = 'SQL'
            and ei.difficulty = 'hard'
            and er.score is not null
    ) t1
where
    rk_asc <> 1
    and rk_desc <> 1
group by
    tag,
    difficulty