题解 | #统计每个学校各难度的用户平均刷题数#

方法①

SELECT

    university,

    difficult_level,

    COUNT(q_p.question_id) / COUNT(DISTINCT q_p.device_id) avg_answer_cnt

FROM user_profile u,

     question_detail q,

     question_practice_detail q_p

WHERE

    u.device_id = q_p.device_id

    and q_p.question_id = q.question_id

GROUP BY

university,difficult_level

方法②

SELECT university,

    difficult_level,

    COUNT(q_p.question_id) / COUNT(DISTINCT q_p.device_id) avg_answer_cnt

FROM question_practice_detail q_p

JOIN user_profile u

ON u.device_id = q_p.device_id

JOIN question_detail q

ON q_p.question_id = q.question_id

GROUP BY

    university,difficult_level