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

with
    temp0 as (
        select
            cc.class_id,
            course_id,
            teacher_id,
            count(ce.student_id) as learners_enrolled
        from
            course_class_ cc
            left join course_enroll_ ce on cc.class_id = ce.class_id
        group by
            cc.class_id,
            course_id,
            teacher_id
    ),
    temp1 as (
        select
            cc.class_id,
            course_id,
            teacher_id,
            count(distinct student_id) as learners_active_m,
            count(distinct if(finished_flag = 1, student_id, null)) as finishers_m,
            round(
                count(distinct if(finished_flag = 1, student_id, null)) / count(distinct student_id),
                2
            ) as completion_rate,
            sum(watch_minutes) as total_minutes_m,
            round(
                sum(watch_minutes) / count(distinct student_id),
                2
            ) as avg_minutes_per_active
        from
            course_class_ cc
            left join study_logs_ sl on cc.class_id = sl.class_id
        where
            date_format(log_ts, '%Y-%m-%d') >= '2024-08-01'
            and date_format(log_ts, '%Y-%m-%d') <= '2024-08-31'
        group by
            cc.class_id,
            course_id,
            teacher_id
    )
select
    t0.class_id,
    t0.course_id,
    t0.teacher_id,
    learners_enrolled,
    ifnull(learners_active_m, 0) as learners_active_m,
    ifnull(finishers_m, 0) as finishers_m,
    ifnull(completion_rate, 0) as completion_rate,
    ifnull(total_minutes_m, 0) as total_minutes_m,
    ifnull(avg_minutes_per_active, 0) as avg_minutes_per_active,
    rank() over (
        partition by
            t0.course_id
        order by
            avg_minutes_per_active desc
    ) as rank_in_course
from
    temp0 t0
    left join temp1 t1 on t0.class_id = t1.class_id
    and t0.course_id = t1.course_id
    and t0.teacher_id = t1.teacher_id
order by
    t0.course_id,
    rank_in_course,
    t0.class_id asc;