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

with
    rank_s as (
        select
            i.tag,
            i.difficulty,
            min(r.score) over (
                partition by
                    i.tag,
                    i.difficulty
            ) as min_score,
            max(r.score) over (
                partition by
                    i.tag,
                    i.difficulty
            ) as max_score,
            r.score
        from
            examination_info i
            inner join exam_record r on i.exam_id = r.exam_id
    )
select
    tag,
    difficulty,
    round(avg(score),1) as clip_avg_score
from rank_s
where score > min_score and score < max_score
group by tag, difficulty