所有用户完成SQL类别高难度试卷得分的截断平均值(考察连接+窗口函数+聚合)

examination_info——exam_id

exam_record 无主键

筛选条件：SQL类别、高难度

select tag, difficulty, round(avg(score),1) clip_avg_score from (select tag, difficulty, score, row_number()over(order by score) r1, row_number()over(order by score desc) r2 from exam_record a left join examination_info b on a.exam_id=b.exam_id where tag='SQL' and difficulty='hard' and submit_time is not null) t where r1<>1 and r2<>1 group by 1,2