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

select
    er.uid,
    round(avg(if (score is null, 0, score)), 0) avg_score,
    round(
        avg(
            truncate (
                if (
                    submit_time is null,
                    duration,
                    (timestampdiff (second, start_time, submit_time)) / 60
                ),
                0
            )
        ),
        1
    ) avg_time_took
from
    test.user_info ui
    join test.exam_record er on ui.uid = er.uid
    join test.examination_info ei on er.exam_id = ei.exam_id
where
    level = 0
    and difficulty = 'hard'
group by
    uid