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

select
    a1.university,
    round(question_id_count / device_id_count, 4) as avg_round
from
    (
        select
            tt1.university,
            count(distinct (device_id)) as device_id_count,
            count(question_id) as question_id_count
        from
            (
                select
                    t1.university,
                    t2.question_id,
                    t1.device_id
                from
                    (
                        select
                            *
                        from
                            user_profile
                    ) t1
                    join (
                        select
                            *
                        from
                            question_practice_detail
                    ) t2 on (t1.device_id = t2.device_id)
            ) tt1
        group by
            tt1.university
    ) a1