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

with t as (select tag, score, difficulty
from exam_record re left 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 order by score asc) as rn_asc,
row_number() over (partition by tag order by score desc) as rn_desc
from t)

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