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

with a1 as (
    select class_id,count(distinct student_id) as learners_enrolled
    from course_enroll_ 
    group by class_id
),
a2 as (
    select class_id,
    count(distinct student_id) as learners_active_m,
    sum(watch_minutes) as total_minutes_m,
    round(ifnull(sum(watch_minutes)/nullif(count(distinct student_id),0),0),2) as avg_minutes_per_active
    from study_logs_
    where date_format(log_ts,'%Y-%m')='2024-08'
    group by class_id
),
a3 as (
    select class_id,count(distinct student_id) as finishers_m
    from study_logs_
    where date_format(log_ts,'%Y-%m')='2024-08' and finished_flag=1
    group by class_id 
),
a4 as (
    select cc.class_id,cc.course_id,cc.teacher_id,
    a1.learners_enrolled,
    coalesce(a2.learners_active_m,0) as learners_active_m,
    coalesce(a3.finishers_m,0) as finishers_m,
    round(ifnull(a3.finishers_m/nullif(a2.learners_active_m,0),0.00),2) as completion_rate,
    coalesce(a2.total_minutes_m,0) as total_minutes_m,
    coalesce(a2.avg_minutes_per_active,0) as avg_minutes_per_active
    from course_class_ cc
    left join a1 on cc.class_id=a1.class_id
    left join a2 on cc.class_id=a2.class_id
    left join a3 on cc.class_id=a3.class_id
)
select class_id ,course_id ,teacher_id ,learners_enrolled ,learners_active_m ,finishers_m ,completion_rate , total_minutes_m ,avg_minutes_per_active,rank() over(partition by course_id order by avg_minutes_per_active desc) as rank_in_course 
from a4 
order by course_id,rank_in_course,class_id;