# 先对每个试卷进行分数的归一化计算，然后按用户和试卷进行分组，计算均值，只保留整数

WITH temp_0 AS(
    SELECT b.id, b.uid, a.exam_id, b.score,
        MAX(score) OVER(PARTITION BY exam_id) max_score, 
        MIN(score) OVER(PARTITION BY exam_id) min_score
    FROM examination_info a
        JOIN exam_record b USING(exam_id)
    WHERE a.difficulty = 'hard' AND b.score IS NOT NULL
    )
    ,temp_2 AS(
    SELECT a.id, a.uid, a.exam_id, a.score, a.max_score, a.min_score, 
            IF(a.max_score = a.min_score, a.score, (a.score - a.min_score) * 100 / (a.max_score - a.min_score)) z_score
            # (a.score - b.min_score) * 100 / (b.max_score - b.min_score)  z_score
    FROM temp_0 a
        
    )


SELECT uid, exam_id, ROUND(AVG(z_score), 0) avg_new_score
FROM temp_2
GROUP BY uid, exam_id
ORDER BY exam_id, avg_new_score DESC

踩的坑：

题目解释中提到 如果某个试卷作答记录中只有一个得分，那么无需使用公式，归一化并缩放后分数仍为原分数，我还以为是使用公式后还是原分数。一直报错用例过不了才溯源到这个问题。 ——如果只有一个得分，还使用该公式，那就成0分了！

我还一直在想是不是取整的问题，说到这，取整也有好几种，最常见的是round，返回离X最近的整数，截断时要进行四舍五入

FLOOR(X)函数 返回不大于X的最大整数，如1.8返回1

CEIL(X)、CEILING(X)函数 返回不小于X的最小整数。 如1.8 返回2

TRUNCATE(X,D)函数 返回数值X保留到小数点后D位的值，截断时不进行四舍五入。

FORMAT(X,D)函数 将数字X格式化，将X保留到小数点后D位，截断时要进行四舍五入。

思路：

1.链接两个表，筛选出高难度试题和作答完成的记录。使用聚合窗口函数提取出每个试卷的最值

2.基于上表，计算出归一化后的值，当然如果只有一个得分，就返回原值

3.最后主查询提取出平均值取整，分组，排序