题目分析

不同学校、不同难度:

  • GROUP BY

学校角度下，用户平均答题量:答题总数/用户总数

  • (count(up.answer_cnt)/count(distinct up.device_id)

题目信息综合：

select qpd.id,qpd.question_id,qd.difficult_level 
from question_practice_detail qpd,question_detail qd 
where qpd.question_id = qd.question_id

最终Sql

SELECT
up.university,
qdt.difficult_level,
round((count(up.answer_cnt)/count(distinct up.device_id)),4) as avg_answer_cnt
from 
user_profile up,
(select 
 qpd.device_id as device_id,
 qpd.question_id as question_id,
 qd.difficult_level as difficult_level
from 
question_practice_detail qpd,
question_detail qd 
where qpd.question_id = qd.question_id) qdt
where 
up.device_id = qdt.device_id
group BY
up.university,
qdt.difficult_level