1. WITH l_content_usage AS (WITH basedata_usage as
2.         (Select usg.date_id, usg.event_session_id sessionid, usg.platform, usg.district_id, usg.school_id, usg.class_id
3.           ,usg.user_id, usg.role_type, usg.product_id, usg.content_id, usg.assignment_id, usg.userassignment_id
4.           , usg.login_datetime, usg.content_opened_datetime, usg.content_submitted_datetime
5.           , usg.content_closed_datetime
6.          from report.fact_content_usage usg
7.         inner join dw.dimorganization org on org.organizationid=usg.school_id
8.            AND 1=1 -- no filter on 'l_content_usage.org_name'
9.  
10.         inner join report.organization_hierarchy as o_hier on usg.district_id = o_hier.parent_organization_id
11.             AND (o_hier.parent_organization_id  in ('8a97b09d3d96517f013fad817713497a') OR o_hier.child_organization_id in ('8a97b09d3d96517f013fad817713497a'))
12.             AND o_hier.parent_organization_type='DISTRICT'
13.             AND ((usg.school_id=o_hier.child_organization_id  and o_hier.child_organization_type='SCHOOL')
14.             OR ( usg.school_id in (Select child_organization_id from report.organization_hierarchy where
15.                 parent_organization_type='SUBDISTRICT' and parent_organization_id in ('8a97b09d3d96517f013fad817713497a') ) ))
16.          inner join report.dim_date dt on dt.date_id=usg.date_id
17.             AND   ((( dt.date_value ) >= ((DATEADD(day,-29, DATE_TRUNC('day',GETDATE()) ))) AND ( dt.date_value ) < ((DATEADD(day,30, DATEADD(day,-29, DATE_TRUNC('day',GETDATE()) ) )))))
18.           where
19.            
20.                1=1
21.            
22.             AND   usg.date_id >=  cast(to_char(DATEADD(day,-29, DATE_TRUNC('day',GETDATE()) ),'yyyymmdd')as integer)
23.             AND   usg.date_id <   cast(to_char(DATEADD(day,30, DATEADD(day,-29, DATE_TRUNC('day',GETDATE()) ) ),'yyyymmdd')as integer)
24.         )
25.     , basedata_score as
26.     (
27.       Select scr.district_id, scr.school_id, scr.user_id, scr.date_id, scr.userassignment_id, scr.content_id
28.       , scr.measure_userscore, scr.measure_maxscore
29.       from report.fact_content_scores scr
30.       inner join basedata_usage usg on usg.district_id=scr.district_id
31.             AND usg.school_id=scr.school_id
32.             AND usg.user_id=scr.user_id
33.             AND usg.userassignment_id=scr.userassignment_id
34.             AND usg.content_id=scr.content_id
35.       where scr.date_id >=  cast(to_char(DATEADD(day,-29, DATE_TRUNC('day',GETDATE()) ),'yyyymmdd')as integer)
36.     )
37.     , basedata as
38.     (
39.       Select usg.date_id, usg.sessionid, usg.platform, usg.district_id, usg.school_id, usg.class_id
40.           ,usg.user_id, usg.role_type, usg.product_id, usg.content_id, usg.assignment_id, usg.userassignment_id
41.           , usg.login_datetime, usg.content_opened_datetime, usg.content_submitted_datetime
42.           , usg.content_closed_datetime,
43.       decode(usg.content_submitted_datetime,null,null,(1.0*scr.measure_userscore)/decode(scr.measure_maxscore ,null ,1 ,0,1,scr.measure_maxscore) ) percentscore
44.       from basedata_usage usg
45.       left join basedata_score scr on usg.district_id=scr.district_id
46.           AND usg.school_id=scr.school_id
47.             AND usg.user_id=scr.user_id
48.             AND usg.date_id <= scr.date_id
49.             AND usg.userassignment_id=scr.userassignment_id
50.             AND usg.content_id=scr.content_id
51.     )
52.        ,distinct_classes as
53.         (
54.             Select distinct class_id,cls.classtitle from basedata
55.             inner join  dw.dimclass cls on basedata.class_id=cls.classid
56.             where (class_id is not null or class_id<>'')
57.         )
58.         ,class_teacher as
59.         (
60.               Select distinct class_id,classtitle,teacherid,usr.firstname_decrypted teacher_firstname,usr.lastname_decrypted teacher_lastname
61.               from(
62.                 Select  cls.class_id,classtitle,enr.user_id teacherid
63.                 from distinct_classes cls
64.                 inner join report.dim_class_teacher_enrollment enr on enr.class_id=cls.class_id
65.                   and enr.is_latest is true
66.                 )qry
67.               inner join dw.dimuser usr on qry.teacherid=usr.userid
68.         )
69.             Select  basedata.sessionid,usr.firstname_decrypted firstname , usr.lastname_decrypted lastname
70.               , usr.loginname_decrypted loginname, usr.email_decrypted email
71.               ,org.organizationname school,basedata.role_type role
72.               ,basedata.product_id,isnull(prd.rumbaproducttitle,basedata.product_id)product_name
73.               ,isnull(prd.program,basedata.product_id)program
74.               ,basedata.class_id,ct.classtitle,ct.teacher_firstname,ct.teacher_lastname
75.               ,cont.contenttitle,cont.contentType
76.               ,basedata.login_datetime logintime, basedata.content_opened_datetime attemptedtime
77.               ,basedata.content_closed_datetime closedtime,basedata.content_submitted_datetime submittime,basedata.percentscore
78.             from basedata
79.             left outer join nexgen.dimrumbaproduct prd on basedata.product_id=prd.rumbaproductid
80.            --left outer join dw.dimclass cls on basedata.class_id=cls.classid
81.             inner join dw.dimcontent cont on basedata.content_id=cont.contentassetid
82.             left outer join class_teacher  ct on basedata.class_id=ct.class_id
83.             inner join dw.dimuser usr on basedata.user_id=usr.userid
84.             inner join dw.dimorganization org on basedata.school_id=org.organizationid
85.           Group by basedata.sessionid,usr.firstname_decrypted  , usr.lastname_decrypted
86.               , usr.loginname_decrypted , usr.email_decrypted
87.               ,org.organizationname ,basedata.role_type
88.               ,basedata.product_id,product_name
89.               ,program
90.               ,basedata.class_id,ct.classtitle,ct.teacher_firstname,ct.teacher_lastname
91.               ,cont.contenttitle,cont.contentType
92.               ,basedata.login_datetime, basedata.content_opened_datetime
93.               ,basedata.content_closed_datetime,basedata.content_submitted_datetime,basedata.percentscore
94.              )
95. SELECT
96.         TO_CHAR(l_content_usage.logintime , 'YYYY-MM-DD HH24:MI:SS') AS "l_content_usage.login_time",
97.         l_content_usage.role  AS "l_content_usage.role",
98.         l_content_usage.loginname  AS "l_content_usage.login_name",
99.         l_content_usage.firstname  AS "l_content_usage.first_name",
100.         l_content_usage.lastname  AS "l_content_usage.last_name",
101.         l_content_usage.email  AS "l_content_usage.email",
102.         l_content_usage.school  AS "l_content_usage.school",
103.         l_content_usage.classtitle  AS "l_content_usage.class",
104.         l_content_usage.teacher_firstname  AS "l_content_usage.teacher_first_name",
105.         l_content_usage.teacher_lastname  AS "l_content_usage.teacher_last_name",
106.         l_content_usage.program  AS "l_content_usage.program",
107.         l_content_usage.product_name  AS "l_content_usage.product",
108.         l_content_usage.contenttitle  AS "l_content_usage.content_name",
109.         l_content_usage.contenttype  AS "l_content_usage.type",
110.         TO_CHAR(l_content_usage.attemptedtime , 'YYYY-MM-DD HH24:MI:SS') AS "l_content_usage.content_open_time",
111.         TO_CHAR(l_content_usage.submittime , 'YYYY-MM-DD HH24:MI:SS') AS "l_content_usage.content_submit_time",
112.         TO_CHAR(l_content_usage.closedtime , 'YYYY-MM-DD HH24:MI:SS') AS "l_content_usage.content_closed_time",
113.         AVG(l_content_usage.percentscore ) AS "l_content_usage.percent_score"
114. FROM l_content_usage
115.  
116. GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
117. ORDER BY 1 DESC
118. LIMIT 5000