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

select
    cc.class_id,
    course_id,
    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 course_id order by avg_minutes_per_active DESC) as rank_in_course
from
    course_class_ as cc
    left join (
        select
            class_id,
            count(student_id) as learners_enrolled
        from
            course_enroll_ as ce
        group by
            class_id
    ) as t1 on cc.class_id = t1.class_id
    left join (
        select
            sl.class_id,
            count(
                distinct case
                    when finished_flag is not null then student_id
                    else null
                end
            ) as learners_active_m,
            COUNT(
                DISTINCT CASE
                    WHEN finished_flag = 1 THEN student_id
                END
            ) as finishers_m,
            round(
                COUNT(
                    DISTINCT CASE
                        WHEN finished_flag = 1 THEN student_id
                    END
                ) / count(
                    distinct case
                        when finished_flag is not null then student_id
                        else null
                    end
                ),
                2
            ) as completion_rate,
            sum(watch_minutes) as total_minutes_m,
            round(
                sum(watch_minutes) / count(
                    distinct case
                        when finished_flag is not null then student_id
                        else null
                    end
                ),
                2
            ) as avg_minutes_per_active
        from
            study_logs_ as sl
        where
            log_ts between '2024-08-01' and '2024-08-31'
        group by
            class_id
    ) as t2 on t1.class_id = t2.class_id
ORDER BY course_id,rank_in_course,class_id ASC