题目分析

  • 表结构：

    • user_profile 表包含用户的基本信息，包括 device_id 和 university。
    • question_practice_detail 表包含用户的题目回答记录，包括 device_id 和 question_id。

  • 目标：

    • 计算每个学校的用户平均答题数量。
    • 按照 university 升序排序结果。

解题思路

  1. 连接表：通过 device_id 连接 user_profile 和 question_practice_detail 表。
  2. 计算答题总次数：统计每个学校的用户在 question_practice_detail 表中的答题总次数。
  3. 计算答过题的用户个数：统计每个学校中在 question_practice_detail 表中出现过的不同用户个数。
  4. 计算平均答题数量：将答题总次数除以答过题的用户个数。
  5. 排序和格式化：按照 university 升序排序，并保留到小数点后4位。

SQL 查询

以下是实现上述思路的 SQL 查询：

SELECT 
    up.university, 
    ROUND(COUNT(qpd.question_id) / COUNT(DISTINCT qpd.device_id), 4) AS avg_answer_cnt
FROM 
    user_profile up
JOIN 
    question_practice_detail qpd ON up.device_id = qpd.device_id
GROUP BY 
    up.university
ORDER BY 
    up.university;