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

SELECT uid,
    ROUND(AVG(score), 0) AS avg_score,
    ROUND(AVG(cost_time), 1) AS avg_time_took
FROM (
    SELECT uid,
        IF(submit_time IS NOT NULL, 
           TIMESTAMPDIFF(MINUTE, start_time, submit_time), 
           duration) AS cost_time,
        IF(score IS NOT NULL, score, 0) AS score
    FROM examination_info
        JOIN exam_record
        USING(exam_id)
        JOIN user_info
        USING(uid)
    WHERE difficulty = 'hard' AND level = 0
)adjust_table
GROUP BY uid