题解 | #每个6/7级用户活跃情况#

with t1 as (
select uid, exam_id, score,
    max(score) over(partition by exam_id) as max_score,
    min(score) over(partition by exam_id) as min_score
from examination_info as a 
right join exam_record as b using(exam_id)
where difficulty = 'hard' and score is not null)

select 
    uid, exam_id, round(avg(std_score)) avg_new_score
from 
(select uid, exam_id, 
    if(max_score != min_score, 
     (score - min_score)/(max_score - min_score) *100,score) as std_score
from t2
) as t2
group by uid, exam_id
order by exam_id , avg_new_score desc

巧妙的一点在于如何判断作答记录只有一个，用if函数判断max(score)和min(score)，如果相等则说明只有一个值，则此时返回原值