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

select
    er.uid,
    ROUND(avg(if (er.score is null, 0, er.score)), 0) avg_score,
    ROUND(
        avg(
            if (
                submit_time is null,
                duration,
                TIMESTAMPDIFF (minute, start_time, submit_time)
            )
        ),
        1
    ) avg_time_took
from
    exam_record er
    inner join (
        select
            exam_id,
            duration
        from
            examination_info
        where
            difficulty = 'hard'
    ) ei on er.exam_id = ei.exam_id
    inner join (
        select
            uid
        from
            user_info
        where
            level = '0'
    ) ui on er.uid = ui.uid
group by
    er.uid