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

SELECT
    ei.exam_id,
    count(distinct er.uid) AS uv,
    round(AVG(er.score),1) AS avg_score
FROM exam_record er
LEFT JOIN user_info ui
    ON er.uid = ui.uid
LEFT JOIN examination_info ei
    ON er.exam_id = ei.exam_id
WHERE ei.tag = 'sql'
  AND DATE(er.submit_time) = DATE(ei.release_time)
  AND ui.level > 5
group by ei.exam_id
order by uv DESC, avg_score ASC;