With
org_list as
        (
                Select o_hier.child_organization_id school_id
                from report.organization_hierarchy o_hier
                    INNER JOIN dw.dimorganization org on   o_hier.child_organization_id =org.organizationid
                Where  o_hier.parent_organization_id in ('8a97b09e3abc60e2013ae60bd43e0073','Rlz_RRSQA_New_Data_Dist_Org001','Rlz_RRSQA_New_Data_Dist_Org002','Rlz_RRSQA_New_Data_Dist_Org003','Rlz_RRSQA_New_Data_Dist_Org004','Rlz_RRSQA_New_Data_Dist_Org005','Rlz_RRSQA_New_Data_Dist_Org006','Rlz_RRSQA_New_Data_Dist_Org007','Rlz_RRSQA_New_Data_Dist_Org008','Rlz_RRSQA_New_Data_Dist_Org009','Rlz_RRSQA_New_Data_Dist_Org010','Rlz_RRSQA_New_Data_Dist_Org011','Rlz_RRSQA_New_Data_Dist_Org012','Rlz_RRSQA_New_Data_Dist_Org013','Rlz_RRSQA_Rub_Dist_Org001','Rlz_RRSQA_Rub_Dist_Org002','Rlz_RRSQA_Rub_Dist_Org003')
                        and o_hier.child_organization_type='SCHOOL'
        )
,login_data as
        (
               Select org_list.school_id,login.user_id ,sum(login.measure_login_count)login_count
        from  org_list
                        LEFT OUTER JOIN report.summary_logins login on org_list.school_id=login.school_id
                                and login.district_id in ('8a97b09e3abc60e2013ae60bd43e0073','Rlz_RRSQA_New_Data_Dist_Org001','Rlz_RRSQA_New_Data_Dist_Org002','Rlz_RRSQA_New_Data_Dist_Org003','Rlz_RRSQA_New_Data_Dist_Org004','Rlz_RRSQA_New_Data_Dist_Org005','Rlz_RRSQA_New_Data_Dist_Org006','Rlz_RRSQA_New_Data_Dist_Org007','Rlz_RRSQA_New_Data_Dist_Org008','Rlz_RRSQA_New_Data_Dist_Org009','Rlz_RRSQA_New_Data_Dist_Org010','Rlz_RRSQA_New_Data_Dist_Org011','Rlz_RRSQA_New_Data_Dist_Org012','Rlz_RRSQA_New_Data_Dist_Org013','Rlz_RRSQA_Rub_Dist_Org001','Rlz_RRSQA_Rub_Dist_Org002','Rlz_RRSQA_Rub_Dist_Org003')
                       INNER JOIN report.dim_date dd ON login.date_id = dd.date_id
        where ((( dd.date_value ) >= (TIMESTAMP '2019-08-01') AND ( dd.date_value ) < (TIMESTAMP '2020-07-30')))
                  group by org_list.school_id,login.user_id
         )
,class_list as
        (
        Select distinct classid class_id,cls.classtitle class_name, cls.organizationid school_id
                 ,contentcontainerid product_id
        from  org_list org
                        INNER JOIN dw.dimclass cls on org.school_id=cls.organizationid
                INNER JOIN dw.DimClassProductAssociation cpa on cls.ClassID = cpa.organizationid
         where cpa.OrganizationType = 'class' and cpa.ContainerType= 'product'
                AND cls.intpartnerid in (Select integrationpartnername  from etl.integrationpartner where integrationpartnerid =7)
                AND cpa.intpartnerid =cls.intpartnerid
                AND  
                       1=1
                     
        )
,distinct_class_list as
        (
                Select class_id, school_id
                from class_list
                group by class_id, school_id
        )
,class_enr as
        (
        select cl.school_id,cl.class_id,count(distinct(ue.user_id))enrolled_Students
        from distinct_class_list cl
                left outer join report.dim_class_student_enrollment ue on cl.class_id=ue.class_id
                group by cl.school_id,cl.class_id
    )
,class_active_cnts as
        (
        select cl.school_id,cl.class_id,count(distinct(ld.user_id))active_Students,sum(ld.login_count) total_logins
        from distinct_class_list cl
                left outer join report.dim_class_student_enrollment ue on cl.class_id=ue.class_id
                                left outer join login_data ld on cl.school_id =ld.school_id and ue.user_id=ld.user_id
                group by cl.school_id,cl.class_id
    )
,class_teacher as
    (
    Select cl.school_id,cl.class_id,cl.class_name,cl.product_id,enr.user_id teacher_id, usr.fullname_decrypted teacher_name
            from  class_list cl
                    left outer join report.dim_class_teacher_enrollment enr on cl.class_id=enr.class_id and enr.is_latest is true
                    left outer join dw.dimuser usr on usr.userid=enr.user_id
    )
Select  cl.school_id,cl.class_id ,cl.class_name, cl.teacher_id, cl.teacher_name
        ,listagg(distinct isnull(rumbaproducttitle,product_id),' , ') within group (order by rumbaproducttitle) product_name
       ,c_enr.enrolled_students
        ,cl_act.active_students
        ,isnull(cl_act.total_logins,0)total_logins
        ,isnull((cl_act.total_logins/cl_act.active_students),0) avg_login_per_student
from class_teacher cl
        inner join class_enr  c_enr  on cl.school_id=c_enr.school_id and cl.class_id=c_enr.class_id
        inner join class_active_cnts cl_act on cl.school_id=cl_act.school_id and cl.class_id=cl_act.class_id
        left outer join nexgen.dimrumbaproduct prd on  cl.product_id=prd.rumbaproductid
group by  cl.school_id,cl.class_id,cl.class_name, cl.teacher_id, cl.teacher_name ,c_enr.enrolled_students ,cl_act.active_students
        ,cl_act.total_logins