题解 | 统计每个学校各难度的用户平均刷题数

select 
    d.university,
    d.difficult_level,
    round(count(d.question_id)/count(distinct device_id),4) AS avg_answer_cnt
from
    (
        select
            a.university,
            a.device_id,
            b.question_id,
            c.difficult_level
        from
            user_profile AS a,
            question_practice_detail AS b,
            question_detail AS c
        where
            a.device_id = b.device_id
            and b.question_id = c.question_id
    )AS d
group by 
    d.university,
    difficult_level

我采用双重嵌套来解题。

内层嵌套负责给出university、device_id、question_id、difficult_level的信息，如下表所示

外层嵌套负责整合新表信息，按 university 和 difficult_level 分组。计算每个组的问题数量与设备数量的比值：COUNT(d.question_id)：统计每个组中的问题数量，COUNT(DISTINCT d.device_id)：统计每个组中的不同设备数量，ROUND(COUNT(d.question_id) / COUNT(DISTINCT d.device_id), 4)：计算比值并四舍五入保留 4 位小数。

最终结果包含以下字段：university，difficult_level，avg_answer_cnt：问题数量与设备数量的比值。