SQL23题解 | #统计每个学校各难度的用户平均刷题数#

#按照学校和难度分组:GROUP BY university,difficult_level
#平均答题数：count(QPDD.question_id) / count(distinct QPDD.device_id)

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

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

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

GROUP BY university, difficult_level;