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

with
    l1 as (
        select
            exam_id,
            tag,
            difficulty
        from
            examination_info
        where
            tag = 'SQL'
            and difficulty = 'hard'
        group by
            exam_id,
            tag,
            difficulty
    ),
    l2 as (
        select
            *
        from
            exam_record
        where
            submit_time is not null
    )

select
    tag,
    difficulty,
    round(avg(score) ,1)as clip_avg_score
from
    (
        select
            tag,
            difficulty,
            score,
            row_number() over (
                partition by
                    tag
                order by
                    score desc
            ) as rank_1,
            row_number() over (
                partition by
                    tag
                order by
                    score asc
            ) as rank_2
        from
            l1 join l2  on l1.exam_id = l2.exam_id
    ) a
where
    rank_1 > 1
    and rank_2 > 1
group by
    tag,
    difficulty