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

select
    university,
    round(
        count(q.question_id) / count(distinct q.device_id),
        4
    ) as avg_answer_cnt
from
    user_profile as u
    inner join question_practice_detail as q on u.device_id = q.device_id
group by
    u.university
order by
    u.university asc;

# -- 官方题解
# SELECT
#     up.university,
#     ROUND(
#         COUNT(qpd.question_id) / COUNT(DISTINCT qpd.device_id),
#         4
#     ) AS avg_answer_cnt
# FROM
#     user_profile up
#     JOIN question_practice_detail qpd ON up.device_id = qpd.device_id
# GROUP BY
#     up.university
# ORDER BY
#     up.university;

round(

        count(q.question_id) / count(distinct q.device_id),

        4

    ) as avg_answer_cnt

学会计算数据