SQL24题解 | #统计每个用户的平均刷题数#

#不同难度,按照难度分组:GROUP BY difficult_level
#平均回答次数:round(count(question_id)/count(distinct device_id), 4)

SELECT university, QD.difficult_level, 
        round(count(QPD.question_id)/count(distinct QPD.device_id), 4) AS avg_answer_cnt
FROM user_profile AS UP

LEFT JOIN question_practice_detail AS QPD
ON UP.device_id = QPD.device_id

LEFT JOIN question_detail AS QD
ON QPD.question_id = QD.question_id

WHERE university = '山东大学'
GROUP BY QD.difficult_level;