题解 | #试卷发布当天作答人数和平均分#
试卷发布当天作答人数和平均分
http://www.nowcoder.com/practice/5b58e89556dc4153a79d8cf8c08ba499
SELECT a.exam_id,COUNT(distinct a.uid) uv,ROUND(avg(a.score),1) avg_score
from exam_record a, examination_info b
where a.exam_id = b.exam_id
and DATE_FORMAT(a.start_time,'%Y%m%d') = DATE_FORMAT(b.release_time,'%Y%m%d')
and a.uid in (select u.uid from user_info u where u.level > 5) and b.tag = 'SQL'
and a.submit_time is not null
group by a.exam_id order by uv desc,avg_score
注意几个细节就可以了:
1. 作答时间和发布时间是同一天
2. 筛选出5级以上的用户
3. 完成了作答的记录