题解 | 统计每个学校的答过题的用户的平均答题数

SELECT 
    b.university,
    ROUND(IFNULL(b.num1 / b.num2, 0), 4) AS avg_answer_cnt
FROM (
    SELECT 
        a.university,
        COUNT(a.question_id) AS num1,
        COUNT(DISTINCT a.device_id) AS num2
        
    FROM (
        SELECT 
            u.university,
            q.device_id,
            q.question_id
        FROM 
            user_profile u
        Left JOIN 
            question_practice_detail q
        ON 
            u.device_id = q.device_id
    ) AS a
    GROUP BY 
        a.university
) AS b
GROUP BY b.university
HAVING avg_answer_cnt <> 0
ORDER BY 
    b.university;

此题目我使用三层嵌套查询，各层查询作用如下所示：

1. 内层查询 (a)：从 user_profile 和 question_practice_detail 表中获取每个用户的university、device_id 和question_ id。使用 LEFT JOIN 连接两个表，确保既保留了有答题记录的用户，也保留了没有答题的用户，避免没有统计到未参与答题的用户
2. 中层查询 (b)：按大学分组，计算每个大学的问题数量 (num1) 和用户数量 (num2)。
3. 外层查询：计算每个大学的 num1 / num2 比值（并使用IFNULL，避免num2的值为0），并四舍五入保留 4 位小数。按大学分组，除去num1/num2的比值为0的大学，最后按大学名称排序。