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

with cte_time_cost as (
    select uid,
    if(score is null,0,score) as new_socre,
    if(submit_time is not null,timestampdiff(minute,start_time,submit_time),duration) as cost_time
    from user_info
    join exam_record using(uid)
    join examination_info using(exam_id)
    where level = 0 and difficulty='hard'
)

select uid,
round(avg(new_socre)) as avg_score,
round(avg(cost_time),1) as avg_time_took
from user_info
join exam_record using(uid)
join examination_info using(exam_id)
join cte_time_cost using(uid)
group by uid