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

WITH t1 AS(    
    SELECT 
        ce.class_id,
        COUNT(DISTINCT ce.student_id) learners_enrolled,
        COUNT(DISTINCT sl.student_id) learners_active_m,
        COUNT(DISTINCT CASE 
                            WHEN finished_flag = 1 THEN sl.student_id
                        END) finishers_m,
        IFNULL(SUM(watch_minutes), 0) total_minutes_m
    FROM course_enroll_ ce 
    LEFT JOIN study_logs_ sl ON ce.student_id = sl.student_id AND ce.class_id = sl.class_id
    AND log_ts BETWEEN '2024-08-01' AND '2024-08-31'  
    GROUP BY ce.class_id
	-- 当按 sl.class_id 分组时，那些在study_logs_中没有8月份记录的班级（如class_id 201）会被分组为NULL，导致数据丢失。
)
SELECT 
    cc.class_id,
    cc.course_id,
    cc.teacher_id,
    learners_enrolled,
    learners_active_m,
    finishers_m,
    ROUND(IFNULL(finishers_m/learners_active_m, 0), 2) completion_rate,
    total_minutes_m,
    ROUND(IFNULL(total_minutes_m/learners_active_m, 0), 2) avg_minutes_per_active,
    RANK()OVER(PARTITION BY course_id ORDER BY ROUND(IFNULL(total_minutes_m/learners_active_m, 0), 2) DESC) rank_in_course
FROM t1 
JOIN course_class_ cc ON cc.class_id = t1.class_id
ORDER BY cc.course_id, rank_in_course, cc.class_id