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

SELECT
    university,
    COUNT(question_id)/count(DISTINCT a.device_id) as avg_answer_cnt
FROM(
    SELECT
        device_id,
        university
    FROM user_profile
) a
JOIN(
    SELECT
        device_id,
        question_id
    FROM question_practice_detail
) b
on a.device_id = b.device_id
GROUP BY university