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

with t as (select re.exam_id, score, tag, difficulty 
from exam_record re inner join examination_info info
on re.exam_id = info.exam_id 
where tag = "SQL" and difficulty = "hard" and score is not null),

t2 as (select tag, difficulty, score,
row_number() over (partition by tag, difficulty order by score desc) as rn_desc,
row_number() over (partition by tag, difficulty order by score asc) as rn_asc
from t)

select tag, difficulty, round(avg(score),1) as clip_avg_score
from t2 where rn_desc != 1 and rn_asc != 1
group by tag, difficulty