题解 | 统计每个学校的答过题的用户的平均答题数

本题的重点在于理解平均答题率，平均答题率 = 总答题次数（不去重）/不同的答题人数（去重）
换言之，比如针对问题111，A、B两位用户都作答了，那么总答题次数记作2，答题人数记作2，这时平均答题率为1.
有了上述思路，我们考虑，
#1. 以答题信息表question_practice_detail 作为主表，left join用户信息表user_profile。 
with a as (
select
    b.university,
    count(a.question_id) as user_answer,
    count(distinct(a.device_id)) as user_number
from question_practice_detail as a
left join user_profile as b on a.device_id = b.device_id
group by b.university
)
select 
    a.university,
    round(avg(a.user_answer/a.user_number),4)as avg_answer_cnt
from a 
group by university 
order by university
;

#2. 以用户信息表user_profile为主表，join答题信息表question_practice_detail。
select
    a.university,
    round(count(b.question_id)/count(distinct(a.device_id)),4) as avg_answer_cnt
from user_profile as a
join question_practice_detail as b on a.device_id = b.device_id
group by a.university
order by a.university
;

加油！！！！