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

# 五级以上用户作答SQL试卷的信息
SELECT ei.exam_id, COUNT(DISTINCT er.uid) AS uv, ROUND(AVG(er.score), 1) AS avg_score # 计算人数和平均分
FROM exam_record AS er
LEFT OUTER JOIN examination_info AS ei
ON er.exam_id = ei.exam_id
LEFT OUTER JOIN user_info AS u
ON er.uid = u.uid
WHERE ei.tag = 'SQL'
AND u.level > 5
AND DATE(er.submit_time) = DATE(ei.release_time) # 发布当天作答SQL试卷的五级用户
GROUP BY ei.exam_id
ORDER BY uv DESC, avg_score;