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

select uid, round(avg(score), 0) as avg_score, round(avg((case when submit_time IS NULL then duration else TIMESTAMPDIFF(MINUTE, start_time, submit_time) end)), 1) as avg_time_took
from (
    select a.uid, a.exam_id, b.difficulty, b.duration, a.start_time, a.submit_time, a.score
    from (
        select uid, exam_id, start_time, submit_time, (case when score IS NULL then 0 else score end) as score
        from exam_record
        where uid in (
            select uid
            from user_info
            where level = 0
        )
    ) as a
    left join examination_info as b
    on a.exam_id = b.exam_id
    where b.difficulty = 'hard'
) as t1
group by uid;