校招算法笔面试 | SQL笔试面试编程题-统计每个学校的答过题的用户的平均答题数

题目

题目链接

题目分析

• 表结构：

  • 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;