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

select 
    university,difficult_level,
    count(*)/count(distinct d.device_id)
from 
    user_profile as u,
    question_practice_detail as d,
    question_detail as qd

where 
    u.device_id=d.device_id and
    d.question_id =qd.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 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;