with a as (
select class_id,count(distinct student_id) learners_active_m1,count(distinct (if(finished_flag=1,student_id,null))) finishers_m1,sum(watch_minutes) total_minutes_m1
from study_logs_
where date_format(log_ts,'%Y%m')=202408
group by class_id),
b as (
select cc.class_id,course_id,teacher_id,ifnull(count(1),0) learners_enrolled
from course_class_ cc 
left join course_enroll_ ce 
on cc.class_id=ce.class_id
group by cc.class_id,course_id,teacher_id)

select b.class_id,course_id,teacher_id,learners_enrolled,ifnull(learners_active_m1,0) learners_active_m,ifnull(finishers_m1,0) finishers_m,ifnull(round(finishers_m1/learners_active_m1,2),0) completion_rate,ifnull(total_minutes_m1,0) total_minutes_m,ifnull(round(total_minutes_m1/learners_active_m1,2),0) avg_minutes_per_active,rank() over(partition by course_id order by total_minutes_m1/learners_active_m1 desc ) rank_in_course
from a 
right join b 
on a.class_id=b.class_id
order by course_id,rank_in_course,b.class_id