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

select 
exam_id
,count(distinct uid) as uv
,round(avg(score), 1) as avg_score
from
(select 
er.uid
,ui.level
,er.exam_id
,ei.release_time
,er.start_time
,er.submit_time
,er.score
,ei.tag
from examination_info as ei left join exam_record as er
    on ei.exam_id = er.exam_id
left join user_info as ui
    on er.uid=ui.uid
where ei.tag='SQL' and er.submit_time is not null and er.start_time > ei.release_time
) as t1
where date_format(t1.release_time, "%Y%m%d") = date_format(t1.start_time, "%Y%m%d") 
    and t1.level > 5 
group by exam_id
order by uv desc, avg_score asc
;