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

# 1.三张表连起来
# 2.明确计算：用户作答的人数 count(distinct uid)
#            平均分 round(avg(score),1)
# 3.group by按照试卷id进行分组
# 4.筛选：level = 5  tag = 'sql'    
# date_format(submit_time,"%Y%m%d") = date_format(release_time,"%Y%m%d")
# 排序

select 
exam_record.exam_id as exam_id,
count(distinct uid) as uv,
round(avg(score),1) as avg_score
from exam_record
left join examination_info using(exam_id)
left join user_info using(uid)
where user_info.level > 5 and  
date_format(exam_record.submit_time,"%Y%m%d") = date_format(examination_info.release_time,"%Y%m%d") 
and examination_info.tag = 'SQL'
group by exam_record.exam_id 
order by uv desc, avg_score