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

with a as(
    select 
        class_id,
        count(student_id) as learners_enrolled
    from course_enroll_  
    group by class_id
),
b as(
    select 
        s.class_id,
        course_id,
        count(distinct student_id ) as learners_active_m,
        count(distinct case when finished_flag=1 then student_id end) as finishers_m,
        ifnull(round((count(distinct case when finished_flag=1 then student_id end))/count(distinct student_id ),2),0) as completion_rate,
        sum(watch_minutes) as total_minutes_m,
        ifnull(round(sum(watch_minutes)/count(distinct student_id),2),0) as avg_minutes_per_active,
        rank() over(partition by course_id order by ifnull(round(sum(watch_minutes)/count(distinct student_id ),2),0) desc) as rank_in_course
    from study_logs_ s join course_class_ using(class_id )
    where date_format(log_ts ,'%Y-%m')='2024-08' 
    group by s.class_id,course_id
)
select 
    c.class_id,
    c.course_id,
    teacher_id,
    learners_enrolled,
    learners_active_m,
    finishers_m,
    completion_rate,
    total_minutes_m,
    avg_minutes_per_active,
    rank_in_course
from course_class_  c
    left join a using(class_id)
    left join b using(class_id)
order by  c.course_id,
    rank_in_course,
    c.class_id;