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

# 每张SQL类别试卷发布后，当天5级以上的用户作答的人数uv和平均分avg_score，按人数降序，相同人数的按平均分升序

#解题思路
#进行三表连接
#发布当天：使用相关子查询，关联至外面的日期

select ei.exam_id ,count(distinct er.uid) as uv ,round(avg(score),1) as avg_score
from examination_info ei join exam_record er
on ei.exam_id = er.exam_id 
join user_info ui
on ui.uid = er.uid
where date(start_time) = (select date(release_time)
                           from examination_info ei2
                           where ei2.exam_id = ei.exam_id)
and level >5 
group by ei.exam_id 
order by uv desc,avg_score asc