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

SELECT
    university,
    difficult_level,
    -- COUNT(question_id),
    -- COUNT(DISTINCT device_id),
    FORMAT (COUNT(question_id) / COUNT(DISTINCT device_id), 4) AS avg_ques_num
FROM
    (
        SELECT
            user_profile.device_id,
            user_profile.university,
            question_practice_detail.question_id,
            question_detail.difficult_level
        FROM
            user_profile,
            question_practice_detail,
            question_detail
        WHERE
            user_profile.device_id = question_practice_detail.device_id
            AND question_practice_detail.question_id = question_detail.question_id
    ) temp
GROUP BY
    temp.difficult_level,
    temp.university
having 
    university = '山东大学'
ORDER BY
    university,
    difficult_level

题解 | #统计每个学校各难度的用户平均刷题数#在该题解的基础上使用having子句 过滤出学校为山东大学的数据。