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

select
    exam_id,
    count(distinct uid) uv,
    round(avg(score),1) avg_score
from (
    select
        ui.uid,
        t1.exam_id,
        t1.score
    from user_info ui 
    join (
        select
            er.uid,
            er.exam_id,
            er.score
        from exam_record er 
        join examination_info ei on er.exam_id =ei.exam_id
        where ei.tag = "SQL"
        and DATE_FORMAT(er.submit_time,'%Y%MYD')  = DATE_FORMAT(ei.release_time,'%Y%MYD') 
    )t1 on ui.uid = t1.uid
    where  ui.level > 5
)t2
group by exam_id
order by uv desc,avg_score