with t as 
(
    select uid,exam_id,
    if(round((score-min(score)over(partition by exam_id))/(max(score)over(partition by exam_id)-min(score)over(partition by exam_id))*100,1) is not null,round((score-min(score)over(partition by exam_id))/(max(score)over(partition by exam_id)-min(score)over(partition by exam_id))*100,1),score) as new_score
    from exam_record
    where exam_id in (select exam_id from examination_info where difficulty='hard') and score is not null
)
select uid,exam_id,round(avg(new_score),0) as avg_new_score
from t
group by uid,exam_id
order by exam_id asc,avg_new_score desc