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

with class_info as (
    select
        cc.class_id,
        cc.course_id,
        cc.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, cc.course_id, cc.teacher_id
),

course_info as (
    select
        cc.class_id,
        count(distinct student_id) as learners_active_m,
        count(distinct case when sl.finished_flag = 1 then student_id end) as finishers_m,
        round(ifnull(count(distinct case when sl.finished_flag = 1 then student_id end) / count(distinct student_id), 0), 2) as completion_rate,
        ifnull(sum(sl.watch_minutes), 0) as total_minutes_m,
        round(ifnull(ifnull(sum(sl.watch_minutes), 0) / count(distinct student_id), 0), 2) as avg_minutes_per_active
    from
        course_class_ cc left join 
        (select * from study_logs_ where log_ts like '%2024-08%') sl on cc.class_id = sl.class_id
    group by
        cc.class_id
)

select 
    cls.*,
    crs.learners_active_m,
    crs.finishers_m,
    crs.completion_rate,
    crs.total_minutes_m,
    crs.avg_minutes_per_active,
    rank() over(partition by cls.course_id order by crs.avg_minutes_per_active desc) as rank_in_course
from
    class_info cls left join course_info crs on cls.class_id = crs.class_id
order by
    cls.course_id, rank_in_course, cls.class_id;