子表嵌套查询 #统计每个学校的答过题的用户的平均答题数#

1. join two tables
2. group by device_id and university, count question_id
3. group by university, calculate the average of question_id

select
    university,
    avg(count_question_id) as avg_answer_cnt
from
    (
        select
            device_id,
            university,
            count(question_id) as count_question_id
        from
            (
                select
                    user.device_id,
                    user.university,
                    question.question_id
                from
                    user_profile as user
                    inner join question_practice_detail as question
                where
                    user.device_id = question.device_id
            ) as sub1
        group by
            device_id,
            university
    ) as sub2
group by
    university
order by
    university