select uid,exam_id,round(avg(new_score),0) as avg_new_score
from(
select *,
case when cnt_score=1 then score else
(score - min_score)/(max_score - min_score)*100 end as new_score

from(
select exam_id,uid,score,
count(score) over(partition by exam_id) as cnt_score,
min(score) over(partition by exam_id) as min_score,
max(score) over(partition by exam_id) as max_score
from exam_record 
where exam_id in
(select exam_id from examination_info where difficulty='hard') 
and score is not null)a)b
group by 1,2 order by 2,3 desc