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

WITH filtered_scores AS (
    SELECT 
        a.tag,
        a.difficulty,
        b.score,
        MAX(b.score) OVER (PARTITION BY a.tag, a.difficulty) AS max_score,
        MIN(b.score) OVER (PARTITION BY a.tag, a.difficulty) AS min_score
    FROM examination_info AS a
    LEFT JOIN exam_record AS b ON a.exam_id = b.exam_id
    WHERE a.tag = 'SQL' AND a.difficulty = 'hard'
)
SELECT 
    tag,
    difficulty,
    ROUND(AVG(score), 1) AS clip_avg_score 
FROM filtered_scores
WHERE score NOT IN (max_score, min_score)
GROUP BY tag, difficulty;