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

select 
a.exam_id,
count(distinct a.uid) uv,
round(sum(a.score)/count(1),1) avg_score

from exam_record a 
left join  examination_info b 
on (a.exam_id=b.exam_id and date(a.submit_time)=date(b.release_time))
left join user_info c on a.uid=c.uid 
where a.score is not null and c.level>5 and b.tag='SQL'
group by a.exam_id
order by uv desc ,avg_score;