【牛客】SQL123SQL类别高难度试卷得分的截断平均值

描述

牛客的运营同学想要查看大家在SQL类别中高难度试卷的得分情况。

请你帮她从exam_record数据表中计算所有用户完成SQL类别高难度试卷得分的截断平均值（去掉一个最大值和一个最小值后的平均值）。

示例数据：examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）

idexam_idtagdifficultydurationrelease_time19001SQLhard602020-01-01 10:00:0029002算法medium802020-08-02 10:00:00

示例数据：exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）

iduidexam_idstart_timesubmit_timescore1100190012020-01-02 09:01:012020-01-02 09:21:01802100190012021-05-02 10:01:012021-05-02 10:30:01813100190012021-06-02 19:01:012021-06-02 19:31:01844100190022021-09-05 19:01:012021-09-05 19:40:01895100190012021-09-02 12:01:01(NULL)(NULL)6100190022021-09-01 12:01:01(NULL)(NULL)7100290022021-02-02 19:01:012021-02-02 19:30:01878100290012021-05-05 18:01:012021-05-05 18:59:02909100390012021-09-07 12:01:012021-09-07 10:31:015010100490012021-09-06 10:01:01(NULL)(NULL)

根据输入你的查询结果如下：

tagdifficultyclip_avg_scoreSQLhard81.7

从examination_info表可知，试卷9001为高难度SQL试卷，该试卷被作答的得分有[80,81,84,90,50]，去除最高分和最低分后为[80,81,84]，平均分为81.6666667，保留一位小数后为81.7

输入描述：

输入数据中至少有3个有效分数

with cte as ( select exam_id,score,tag,difficulty, dense_rank() over (partition by exam_id order by score) as rnk1, dense_rank() over (partition by exam_id order by score desc) as rnk2 from exam_record left join examination_info using (exam_id) where score is not null and tag = 'SQL'and difficulty = 'hard' ) select tag,difficulty, round(avg(score),1) as clip_avg_score from cte where score between (select score from cte where rnk1=2) and (select score from cte where rnk2=2) group by tag,difficulty

或：

with cte as ( select exam_id,score,tag,difficulty, dense_rank() over (partition by exam_id order by score) as rnk1, dense_rank() over (partition by exam_id order by score desc) as rnk2 from exam_record left join examination_info using (exam_id) where score is not null and tag = 'SQL'and difficulty = 'hard' ) select tag,difficulty, round(avg(score),1) as clip_avg_score from cte where rnk1!=1 and rnk2!=1 group by tag,difficulty