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

select
    exam_id,
    count(distinct uid) as uv,
    round(avg(score), 1) as avg_score
from
    exam_record er
    left join examination_info ei using (exam_id)
    left JOIN user_info ui using (uid)
where
    tag = 'SQL'
    and score is not null
    and uid in (
        select
            uid
        from
            user_info
        where
            level > 5
    )
group by
    exam_id
order by uv desc,avg_score asc
    ;