题解 | #0级用户高难度试卷的平均用时和平均得分#

select t5.uid,round(avg(score),0),round(avg(took),1)
from 
    (select t4.uid uid,
           case when score is null then 0
           else score end as score,
           if(submit_time is null,duration,timestampdiff(minute,start_time,submit_time)) as took
    from 
        (select t1.uid uid,start_time,submit_time,duration,score
        from exam_record t1
        join examination_info t2
        on t1.exam_id=t2.exam_id
        join user_info t3
        on t1.uid=t3.uid
        where level=0 and difficulty='hard'
        ) t4
    ) t5
group by t5.uid;