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

select
    t1.exam_id,
    count(distinct t1.uid) as uv,
    round(avg(ifnull(score,0)),1) as avg_score
from
    exam_record t1
    left join examination_info t2 on t1.exam_id = t2.exam_id
    left join user_info t3 on t1.uid = t3.uid
where
    tag = "SQL"
    AND level > 5
    AND date(submit_time) in 
    (select date(release_time) from examination_info where tag = "SQL" )
group by
    t1.exam_id
order by uv desc,avg_score asc