1. With
2. org_list as
3.         (
4.                 Select o_hier.child_organization_id school_id
5.                 from report.organization_hierarchy o_hier
6.                     INNER JOIN dw.dimorganization org on   o_hier.child_organization_id =org.organizationid
7.                 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')
8.                         and o_hier.child_organization_type='SCHOOL'
9.         )
10. ,login_data as
11.         (
12.                Select org_list.school_id,login.user_id ,sum(login.measure_login_count)login_count
13.         from  org_list
14.                         LEFT OUTER JOIN report.summary_logins login on org_list.school_id=login.school_id
15.                                 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')
16.                        INNER JOIN report.dim_date dd ON login.date_id = dd.date_id
17.         where ((( dd.date_value ) >= (TIMESTAMP '2019-08-01') AND ( dd.date_value ) < (TIMESTAMP '2020-07-30')))
18.                   group by org_list.school_id,login.user_id
19.          )
20. ,class_list as
21.         (
22.         Select distinct classid class_id,cls.classtitle class_name, cls.organizationid school_id
23.                  ,contentcontainerid product_id
24.         from  org_list org
25.                         INNER JOIN dw.dimclass cls on org.school_id=cls.organizationid
26.                 INNER JOIN dw.DimClassProductAssociation cpa on cls.ClassID = cpa.organizationid
27.          where cpa.OrganizationType = 'class' and cpa.ContainerType= 'product'
28.                 AND cls.intpartnerid in (Select integrationpartnername  from etl.integrationpartner where integrationpartnerid =7)
29.                 AND cpa.intpartnerid =cls.intpartnerid
30.                 AND  
31.                        1=1
32.                      
33.         )
34. ,distinct_class_list as
35.         (
36.                 Select class_id, school_id
37.                 from class_list
38.                 group by class_id, school_id
39.         )
40. ,class_enr as
41.         (
42.         select cl.school_id,cl.class_id,count(distinct(ue.user_id))enrolled_Students
43.         from distinct_class_list cl
44.                 left outer join report.dim_class_student_enrollment ue on cl.class_id=ue.class_id
45.                 group by cl.school_id,cl.class_id
46.     )
47. ,class_active_cnts as
48.         (
49.         select cl.school_id,cl.class_id,count(distinct(ld.user_id))active_Students,sum(ld.login_count) total_logins
50.         from distinct_class_list cl
51.                 left outer join report.dim_class_student_enrollment ue on cl.class_id=ue.class_id
52.                                 left outer join login_data ld on cl.school_id =ld.school_id and ue.user_id=ld.user_id
53.                 group by cl.school_id,cl.class_id
54.     )
55. ,class_teacher as
56.     (
57.     Select cl.school_id,cl.class_id,cl.class_name,cl.product_id,enr.user_id teacher_id, usr.fullname_decrypted teacher_name
58.             from  class_list cl
59.                     left outer join report.dim_class_teacher_enrollment enr on cl.class_id=enr.class_id and enr.is_latest is true
60.                     left outer join dw.dimuser usr on usr.userid=enr.user_id
61.     )
62. Select  cl.school_id,cl.class_id ,cl.class_name, cl.teacher_id, cl.teacher_name
63.         ,listagg(distinct isnull(rumbaproducttitle,product_id),' , ') within group (order by rumbaproducttitle) product_name
64.        ,c_enr.enrolled_students
65.         ,cl_act.active_students
66.         ,isnull(cl_act.total_logins,0)total_logins
67.         ,isnull((cl_act.total_logins/cl_act.active_students),0) avg_login_per_student
68. from class_teacher cl
69.         inner join class_enr  c_enr  on cl.school_id=c_enr.school_id and cl.class_id=c_enr.class_id
70.         inner join class_active_cnts cl_act on cl.school_id=cl_act.school_id and cl.class_id=cl_act.class_id
71.         left outer join nexgen.dimrumbaproduct prd on  cl.product_id=prd.rumbaproductid
72. 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
73.         ,cl_act.total_logins