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

select ei.exam_id
     , count(distinct er.uid) uv
     , round(avg(score), 1)   avg_score
from exam_record er
         inner join examination_info ei on er.exam_id = ei.exam_id
         inner join user_info ui on er.uid = ui.uid
where ui.level > 5
  and ei.tag = 'SQL'
  and date_format(er.start_time, '%Y-%m-%d') = date_format(ei.release_time, '%Y-%m-%d')
group by ei.exam_id
order by uv desc, avg_score;