with lsb1 as 
(select cc.class_id, cc.course_id, cc.teacher_id, count(student_id) as learners_enrolled
from course_class_ cc
join course_enroll_ ce
on cc.class_id = ce.class_id
group by cc.class_id, cc.course_id, cc.teacher_id),

lsb2 as (select cc.class_id, 
(case when t.learners_active_m is null then 0 else t.learners_active_m end) as learners_active_m,
(case when t.finishers_m is null then 0 else t.finishers_m end) as finishers_m,
round((case when t.learners_active_m is null then 0 else t.finishers_m/t.learners_active_m end), 2) as  completion_rate,
(case when t.total_minutes_m is null then 0 else t.total_minutes_m end) as total_minutes_m,
round((case when t.total_minutes_m is null then 0 else t.total_minutes_m/t.learners_active_m end), 2) as avg_minutes_per_active
from course_class_ cc
left join (select class_id, count(distinct student_id) as learners_active_m, sum(finished_flag) as finishers_m,
sum(watch_minutes) as total_minutes_m
from study_logs_
where date_format(log_ts, '%Y-%m') = '2024-08'
group by class_id) t
on cc.class_id = t.class_id)
select l1.class_id,
l1.course_id,
l1.teacher_id,
l1.learners_enrolled,
l2.learners_active_m,
l2.finishers_m,
l2.completion_rate,
l2.total_minutes_m,
l2.avg_minutes_per_active,
rank() over(partition by l1.course_id order by l2.avg_minutes_per_active desc) as rank_in_course
from lsb1 l1 
join lsb2 l2 
on l1.class_id = l2.class_id
order by l1.course_id, rank_in_course, l1.class_id