题解 | #试卷发布当天作答人数和平均分#

select
    a.exam_id,
    count(distinct a.uid) uv,
    round(avg(a.score), 1) avg_score
from
    (
        select
            t1.exam_id,
            t2.uid,
            t2.score
        from
            examination_info t1
            join exam_record t2 on t1.exam_id = t2.exam_id
            and date (t1.release_time) = date (start_time)
            and t1.tag = 'SQL'
    ) a
    join user_info b on a.uid = b.uid
    and b.level > 5
group by
    a.exam_id
order by
    uv desc,
    avg_score asc