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

# select
#     university
#     count(difficult_level)/count(distint qpd.question_id) as avg_answer_cnt
# from question_practice_detail as qpd
# inner join question_detail as up on qpd.question_id=up.question_id
#到这里思路还不清晰


#可以先看需要的输出是什么，是按照哪写相来分组的；比如按照university和level来分，那么行数应该为university的种类数量*level的种类数，题目中为4*2=8（包括null的行）
#然后看分组之后要怎样计算每一行对应的值
#将计算方法写出
#最后按照先计算，再联结，最后Group的顺序书写代码

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

left join user_profile as up
on up.device_id=qpd.device_id

left join question_detail as qd 
on qd.question_id=qpd.question_id

group by university,difficult_level