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

select er.exam_id, count(distinct er.uid) uv,
    round(avg(er.score),1) avg_score
from examination_info ei join exam_record er 
on ei.exam_id = er.exam_id
where ei.tag = 'SQL'
    and date(ei.release_time) = date(er.submit_time)
    and uid in (
        -- 5级以上的用户
        select uid
        from user_info
        where level > 5
    )
group by ei.exam_id
order by uv desc, avg_score;