**-题解 | #统计每个用户的平均刷题数#

# 问题分解：
# 限定条件：山东大学的用户 up.university="山东大学"；
# 不同难度：按难度分组group by difficult_level
# 平均答题数：总答题数除以总人数count(qpd.question_id) / count(distinct qpd.device_id) 来自上面信息三个表，需要联表，up与qpd用device_id连接并限定大学，qd与qpd用question_id连接。



select university,difficult_level,
round(count(b.question_id) / count(distinct b.device_id),4)
as	avg_answer_cnt


from  user_profile a ,question_practice_detail b,question_detail c
where a.device_id=b.device_id and b.question_id=c.question_id
and university='山东大学'
group by difficult_level;


/*
SELECT
    t1.university,
    t3.difficult_level,
    COUNT(t2.question_id) / COUNT(DISTINCT(t2.device_id)) as avg_answer_cnt
from
    user_profile as t1,
    question_practice_detail as t2,
    question_detail as t3
WHERE
    t1.university = '山东大学'
    and t1.device_id = t2.device_id
    and t2.question_id = t3.question_id
GROUP BY
    t3.difficult_level;*/