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

select
    exam_id,
    count(distinct uid) uv,
    round(avg(score),1) avg_score
from
(
    select t1.exam_id,

       uid,
       score
from
    (
        select
            exam_id,
            date_format(release_time,'%Y-%m-%d')release_time,
            tag
        from examination_info

    )t1
join
(
    select
        exam_id,
        uid,
        date_format(submit_time,'%Y-%m-%d')submit_time,
        score
    from exam_record
    where submit_time is not null
)t2
on t1.exam_id=t2.exam_id
where release_time=submit_time and uid in
    (
        select uid from user_info where level>5
    )

)t3
group by exam_id
order by uv desc ,avg_score ;