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

with tab as( -- 整合初级信息
select ce.student_id,cc.class_id,cc.course_id,cc.teacher_id,s.lesson_id,s.watch_minutes,s.finished_flag,s.log_ts
from course_enroll_ as ce
left join course_class_ as cc
on cc.class_id = ce.class_id
left join study_logs_ as s
on ce.student_id = s.student_id and ce.class_id = s.class_id 
where s.log_ts like '2024-08%' or s.log_ts is null
),
learn_roll_active as( -- 统计各课程的报名人数
select ce.class_id,cc.course_id,cc.teacher_id,count(distinct ce.student_id) as learners_enrolled
from course_enroll_ as ce
left join course_class_ as cc
on ce.class_id = cc.class_id
group by ce.class_id,cc.course_id,cc.teacher_id
),
senior_info as
( -- 统计出唯一键班级、课程、教室和对应的班级人数、有学习记录人数、完成过课程人数、总学习时间。
select 
t.class_id,
t.course_id,
t.teacher_id,
lra.learners_enrolled,
t.learners_active_m,
t.finishers_m,
if(t.total_minutes_m is not null , t.total_minutes_m,  0 ) as total_minutes_m
from(
select -- 统计至少学习过一次的学员、有完成记录的学员个数、总学习时间。
class_id, 
course_id,
teacher_id,
count(distinct case when log_ts is not null then student_id else null end) as learners_active_m,
count(distinct case when log_ts is not null and finished_flag =1 then student_id else null end) as finishers_m,
sum(watch_minutes) as total_minutes_m
from tab 
group by class_id,course_id,teacher_id
) as t
left join learn_roll_active as lra
on t.class_id = lra.class_id and t.course_id = lra.course_id and t.teacher_id = lra.teacher_id
)
select -- 最终产生排名并调整展示顺序
*,
rank() over(partition by course_id order by avg_minutes_per_active desc) as rank_in_course
from
(
select -- 统计完课率、人均学习时长
class_id,
course_id,
teacher_id,
learners_enrolled,
learners_active_m,
finishers_m,
if(learners_active_m = 0 ,format(0,2),round(finishers_m/learners_active_m,2)) as completion_rate,
total_minutes_m,
if(learners_active_m = 0,format(0,2),round(total_minutes_m/learners_active_m,2)) as avg_minutes_per_active
from senior_info 
) as temp
order by course_id asc,rank_in_course asc,class_id asc