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

#course_class_ 、course_enroll_、study_logs_
# class_id、student_id
# 目标月（2024-08）每个班级的关键指标与在同课程内的排名
# 每个班级
#  course_enroll_ learners_enrolled
#  study_logs_ earners_active_m、finishers_m、completion_rate、total_minutes_m、avg_minutes_per_active
# 1、 计算注册人数
with enrolled as (
select  class_id,
        count(enroll_date) as learners_enrolled
from course_enroll_ 
# 总注册人数而不是8月注册人数
# where enroll_date between '2024-08-01' and '2024-08-31'
group by class_id),
# 2、 计算学习相关指标
learners as (
select  class_id,
        count(distinct student_id) as learners_active_m,
        sum(case when finished_flag = 1 then 1 else 0 end) as finishers_m,
        round(sum(case when finished_flag = 1 then 1 else 0 end) / count(distinct student_id),2) as completion_rate,
        sum(watch_minutes) as total_minutes_m,
        round(sum(watch_minutes) / count(distinct student_id),2) as avg_minutes_per_active
from study_logs_
# 时间范围为8月
where date(log_ts) between '2024-08-01' and '2024-08-31'
group by class_id)

select c.class_id,c.course_id,c.teacher_id,
       e.learners_enrolled,
       ifnull(l.learners_active_m,0) as  learners_active_m,
       ifnull(l.finishers_m,0) as  finishers_m,
       ifnull(l.completion_rate,0) as  completion_rate,
       ifnull(l.total_minutes_m,0) as  total_minutes_m,
       ifnull(l.avg_minutes_per_active,0) as  avg_minutes_per_active,
       # 最后再排名，存在只注册不学习的情况
       rank() over (partition by c.course_id order by l.avg_minutes_per_active desc) as rank_in_course
from course_class_ c
left join enrolled e on e.class_id = c.class_id
left join learners l on l.class_id = c.class_id
order by c.course_id asc,rank_in_course asc,c.class_id asc