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

with a1 as(
    select 
    class_id,
    course_id,
    teacher_id,
    count(distinct student_id) 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 student_id)),2) as completion_rate,
    SUM(watch_minutes) as total_minutes_m,
    round(coalesce(SUM(watch_minutes)/count(distinct student_id),0),2)  as avg_minutes_per_active,
    rank() over(partition by course_id order by (coalesce(SUM(watch_minutes)/count(distinct student_id),0)) desc) as rank_in_course
    from study_logs_ c 
    join course_class_ a using(class_id)
    where date_format(log_ts,'%Y-%m')='2024-08'
    group by class_id,course_id,teacher_id
),

a2 as(
    select 
    class_id,
    count(distinct student_id) as learners_enrolled
    from course_enroll_ b 
    group by 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_in_course
from a2 
join a1 using(class_id)
order by course_id,rank_in_course,class_id