select  
   university,
   count(question_id) / count(distinct qpd.device_id) as avg_answer_cnt
from user_profile as up
right join question_practice_detail as qpd on up.device_id = qpd.device_id
group by university
order by university asc

右连接

题目中显示，“看到答过题目”的用户，就需要联想到右连接，然后看到用户的平均答题数就需要联想到分组

distinct去除重复答题的用户

COUNT(DISTINCT(q.device_id))就是取出答过题目的去除重复的用户数量

COUNT(q.question_id)就是答过题目的总用户总数量，

COUNT(q.question_id)/COUNT(DISTINCT(q.device_id))就是答过题的用户的平均答题数