题解 | #试卷发布当天作答人数和平均分#
试卷发布当天作答人数和平均分
http://www.nowcoder.com/practice/5b58e89556dc4153a79d8cf8c08ba499
一、需求分析
- 计算当天5级以上SQL类别的试卷的作答人数UV，平均分avg_score
- 输出：exam_id,uv,avg_score （order by uv desc,avg_score asc)
二、思路
- 作答信息新增字段作答日期，内连接试卷表，选择考试tag为sql且试卷发行日期与作答日期相同的记录
- 内连接用户信息表，筛选等级>5的用户
- 处理输出结果：字段顺序，小数点，排序
三、代码实现
select t1.exam_id,count(distinct t1.uid) uv,round(avg(t1.score),1) avg_score
from
(select distinct *,date_format(submit_time,'%Y-%m-%d') t_r # 作答时间
from exam_record)t1
inner join
(select DISTINCT exam_id,date_format(release_time,'%Y-%m-%d') t_i
from examination_info
where tag = 'SQL') t2
on t1.exam_id = t2.exam_id and t1.t_r = t2.t_i
inner join (select distinct uid from user_info where level >5) t3
on t1.uid = t3.uid
group by t1.exam_id
order by uv desc,avg_score asc