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

分表分的有点麻烦 t1和t2还能在处理一下

with t1 as (
    select student_id,class_id,lesson_id,watch_minutes,finished_flag,log_ts
    from study_logs_ 
    where finished_flag = 1 and mid(log_ts,1,7) = '2024-08'
),
t2 as (
    select student_id,class_id,lesson_id,watch_minutes,finished_flag,log_ts
    from study_logs_ 
    where mid(log_ts,1,7) = '2024-08'
),
t3 as (
    select ce.class_id,course_id,teacher_id,
    count(distinct ce.student_id) learners_enrolled,
    count(distinct t2.student_id) learners_active_m,
    count(distinct t1.student_id) finishers_m,
    rank()over(partition by course_id order by ifnull(round(ifnull(sum(t2.watch_minutes),0)/count(distinct t2.student_id),2),0) desc) rank_in_course
    from course_enroll_ ce 
    left join course_class_ cc 
    on ce.class_id = cc.class_id
    left join t1
    on ce.student_id = t1.student_id and ce.class_id = t1.class_id
    left join t2
    on ce.student_id = t2.student_id and ce.class_id = t2.class_id
    group by ce.class_id,course_id,teacher_id
    order by course_id,rank_in_course,class_id    
)
select t3.class_id,course_id,teacher_id,learners_enrolled,learners_active_m,finishers_m,
ifnull(round(finishers_m/learners_active_m,2),0) completion_rate,
ifnull(sum(t2.watch_minutes),0) total_minutes_m,
ifnull(round(sum(t2.watch_minutes)/learners_active_m,2),0) avg_minutes_per_active,
rank_in_course
from t3 left join t2 on t3.class_id = t2.class_id
group by t3.class_id,course_id,teacher_id