题解 | #0级用户高难度试卷的平均用时和平均得分#

SELECT exam_record.uid
     , ROUND(SUM(score) / COUNT(start_time)) AS avg_score
     , ROUND(SUM(TIMESTAMPDIFF(MINUTE,start_time,
                               IF(submit_time IS NULL,DATE_ADD(start_time,INTERVAL duration MINUTE),submit_time))) /
             COUNT(start_time),1)            AS avg_time_took
FROM exam_record JOIN user_info USING (uid)
                 JOIN examination_info USING (exam_id)
WHERE level = 0
  AND difficulty = 'hard'
GROUP BY exam_record.uid