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

select
    tag,
    difficulty,
    round(avg(score),1) clip_avg_score
from
    (
        select
            tag,
            difficulty,
            ei.exam_id,
            score,
            case
                when score = max(score) over ()
                or score = min(score) over () then 0
                else 1
            end as is_effect
        from
            examination_info ei
            right join exam_record er on ei.exam_id = er.exam_id
        where
            difficulty = 'hard'
            and tag = 'SQL'

    ) t
where is_effect = 1
group by
    tag,
    difficulty