题解 | 试卷发布当天作答人数和平均分
试卷发布当天作答人数和平均分
https://www.nowcoder.com/practice/5b58e89556dc4153a79d8cf8c08ba499
select
er.exam_id,
count(distinct er.uid) as uv,
round(avg(score),1) as avg_score
from exam_record er
inner join user_info ui
on ui.uid = er.uid
inner join examination_info ei
on ei.exam_id = er.exam_id
where ei.tag='SQL' and ui.level>5
and DATE_FORMAT(ei.release_time,'%Y%m%d') = DATE_FORMAT(er.submit_time,'%Y%m%d')
group by er.exam_id
order by uv desc, avg_score asc
