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

with t as (
select class_id,course_id,teacher_id,
count(distinct student_id) as learners_enrolled,
sum(if(date_format(log_ts,'%Y-%m-%d') between '2024-08-01' and '2024-08-31',watch_minutes,0)) as total_minutes_m
from course_class_
join course_enroll_ using(class_id)
left join study_logs_ using(class_id,student_id)
group by class_id,course_id,teacher_id
),

t1 as (
select class_id,course_id,teacher_id,
count(distinct student_id) as learners_active_m
from course_class_
left join study_logs_ using(class_id)
where date_format(log_ts,'%Y-%m-%d') between '2024-08-01' and '2024-08-31'
group by class_id,course_id,teacher_id
),

t2 as(
select class_id,course_id,teacher_id,
count(distinct student_id) as finishers_m
from course_class_
left join study_logs_ using(class_id)
where finished_flag = 1 and (date_format(log_ts,'%Y-%m-%d') between '2024-08-01' and '2024-08-31')
group by class_id,course_id,teacher_id
)

select class_id,course_id,teacher_id,
ifnull(learners_enrolled,0) as learners_enrolled,
ifnull(learners_active_m,0) as learners_active_m,
ifnull(finishers_m,0) as finishers_m,
round(ifnull(finishers_m/nullif(learners_active_m,0),0),2) as completion_rate,
ifnull(total_minutes_m,0) as total_minutes_m,
round(ifnull(total_minutes_m/nullif(learners_active_m,0),0),2) as avg_minutes_per_active,
rank()over(partition by course_id order by round(ifnull(total_minutes_m/nullif(learners_active_m,0),0),2) desc) as rank_in_course
from t 
left join t1 using(class_id,course_id,teacher_id)
left join t2 using(class_id,course_id,teacher_id)