题解 | #月总刷题数和日均刷题数#

```WITH t AS (
	SELECT
		a.uid,
		a.score,
		b.exam_id,
	IF
		(
			( a.score - MIN( score ) over ( PARTITION BY exam_id ) ) / ( MAX( score ) over ( PARTITION BY exam_id ) - MIN( score ) over ( PARTITION BY exam_id ) ) IS NOT NULL,
			( a.score - MIN( score ) over ( PARTITION BY exam_id ) ) * 100 / ( MAX( score ) over ( PARTITION BY exam_id ) - MIN( score ) over ( PARTITION BY exam_id ) ),
			a.score 
		) minmaxscore 
	FROM
		exam_record a
		LEFT JOIN examination_info b ON a.exam_id = b.exam_id 
	WHERE
		difficulty = "hard" 
		AND score IS NOT NULL 
	) SELECT
	uid,
	exam_id,
	ROUND( avg( minmaxscore ), 0 ) avg_new_score 
FROM
	t 
GROUP BY
	exam_id,
	uid 
ORDER BY
	exam_id ASC,
	avg_new_score DESC;