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

# 零级用户 高难度试卷 平均得分 平均用时
# 未完成的试卷按照最长时间和零分处理
select
    ui.uid,
    round(avg(
        case
            when er.score is null then 0
            else er.score
        end
    ),0) as avg_score,
    round(avg(
        case
            when submit_time is null then ei.duration
            else timestampdiff(minute,er.start_time,er.submit_time)
        end
    ),1) as avg_time_took
from
    user_info as ui
    inner join
    exam_record as er using(uid)
    inner join
    examination_info as ei using(exam_id)
where
    ui.level=0
    and
    ei.difficulty='hard'
group by
    ui.uid