题解 | SQL21 #试卷发布当天作答人数和平均分#
试卷发布当天作答人数和平均分
http://www.nowcoder.com/practice/5b58e89556dc4153a79d8cf8c08ba499
分析：
- SQL类别——where tag='SQL'
- 发布当天——date(release_time)=date(submit_time)
- 5级以上——where level>5
- 作答人数——count(distinct uid) as uv (desc)
- 平均分——avg(score) as avg_score (asc)
提交答案：
select er.exam_id,count(distinct er.uid) as uv,
round(avg(score),1) as avg_score
from exam_record as er
join user_info as ui
on ui.uid=er.uid
join examination_info as ei
on er.exam_id=ei.exam_id
and date(submit_time)=date(release_time)
where level>5 and tag='SQL'and submit_time is not null
group by exam_id
order by uv desc,avg_score
