题解 | #SQL类别高难度试卷得分的截断平均值#
SQL类别高难度试卷得分的截断平均值
https://www.nowcoder.com/practice/a690f76a718242fd80757115d305be45
select tag,difficulty,round(sum(score)/count(score),1) clip_avg_score from
(select
uid,exam_id,score,tag,difficulty,
first_value(score) over(order by score desc rows between unbounded preceding and unbounded following) max_value,
last_value(score) over(order by score desc rows between unbounded preceding and unbounded following) min_value
from
(select uid,ei.exam_id exam_id,score,tag,difficulty from exam_record er
left join
examination_info ei
on er.exam_id = ei.exam_id
where
ei.tag = 'SQL'
and ei.difficulty = 'hard'
and score is not null) a) t
where t.score max_value and t.score min_value
group by tag,difficulty
