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

select
    e.exam_id,
    count(distinct u.uid) as uv,
    round(avg(score),1) as avg_score
from
    exam_record as e
    inner join user_info as u on e.uid = u.uid
    inner join examination_info as i on i.exam_id = e.exam_id
where
    u.level > 5
    and e.submit_time is not null
    and i.tag = 'SQL'
    and date_format (i.release_time, '%Y%m') = date_format (e.submit_time, '%Y%m')
group by
    exam_id
order by
    uv desc,
    avg_score