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

select
uid,
format(avg(score),0) avg_score,
format(avg(timestampdiff(minute,start_time,submit_time)),1) avg_time_took
from (
    select
a.uid,
c.start_time,
case when c.submit_time is null then date_add(c.start_time,interval b.duration minute)
else c.submit_time end submit_time,
case when c.score is null then 0
else score end score
from 
exam_record c
left join 
user_info a
on c.uid=a.uid
left join examination_info b
on c.exam_id=b.exam_id
where a.level=0
and b.difficulty='hard'
 ) d
 group by uid

# group by a.uid