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

select tag,difficulty,round(avg(score),1)
from (
    select uid,exam_record.exam_id,score,tag,difficulty,
    percent_rank() over(partition by exam_id order by score) as pr
    from exam_record left join examination_info using(exam_id)
    where tag='SQL' and difficulty = 'hard' and score is not null) exam
where pr <>0 and pr <> 1
group by tag