题解 | 统计每个班级的关键指标

with
    t1 as (
        select
            class_id,
            count(student_id) as learners_enrolled
        from
            course_enroll_
        group by
            class_id
    ),
    t2 as (
        select
            class_id,
            count(distinct student_id) as learners_active_m,
            COUNT(
                DISTINCT CASE
                    WHEN finished_flag = 1 THEN student_id else null
                END
            ) as finishers_m,
            sum(watch_minutes) as total_minutes_m
        from
            study_logs_
        where date(log_ts) between '2024-08-01' and '2024-08-31'
        group by
            class_id
    )
select
    cc.class_id,
    course_id,
    teacher_id,
    t1.learners_enrolled,
    ifnull(t2.learners_active_m, 0) as learners_active_m,
    ifnull(t2.finishers_m, 0) as finishers_m,
    COALESCE(ROUND(finishers_m / NULLIF(learners_active_m, 0), 2), 0.00) as completion_rate,
    ifnull(total_minutes_m, 0) as total_minutes_m,
    COALESCE(ROUND(IFNULL(total_minutes_m, 0) / NULLIF(learners_active_m, 0), 2), 0.00) as avg_minutes_per_active,
    RANK() over (
        partition by
            cc.course_id
        order by
            total_minutes_m / learners_active_m desc
    ) as rank_in_course
from
    course_class_ cc
    left join t1 on t1.class_id = cc.class_id
    left join t2 on t2.class_id = cc.class_id
order by
    course_id,
    rank_in_course,
    class_id;