WITH l_content_usage AS (WITH basedata_usage as
        (Select usg.date_id, usg.event_session_id sessionid, usg.platform, usg.district_id, usg.school_id, usg.class_id
          ,usg.user_id, usg.role_type, usg.product_id, usg.content_id, usg.assignment_id, usg.userassignment_id
          , usg.login_datetime, usg.content_opened_datetime, usg.content_submitted_datetime
          , usg.content_closed_datetime
         from report.fact_content_usage usg
        inner join dw.dimorganization org on org.organizationid=usg.school_id
           AND 1=1 -- no filter on 'l_content_usage.org_name' 

        inner join report.organization_hierarchy as o_hier on usg.district_id = o_hier.parent_organization_id
            AND (o_hier.parent_organization_id  in ('8a97b09d3d96517f013fad817713497a') OR o_hier.child_organization_id in ('8a97b09d3d96517f013fad817713497a'))
            AND o_hier.parent_organization_type='DISTRICT'
            AND ((usg.school_id=o_hier.child_organization_id  and o_hier.child_organization_type='SCHOOL')
            OR ( usg.school_id in (Select child_organization_id from report.organization_hierarchy where
                parent_organization_type='SUBDISTRICT' and parent_organization_id in ('8a97b09d3d96517f013fad817713497a') ) ))
         inner join report.dim_date dt on dt.date_id=usg.date_id
            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()) ) )))))
          where
           
               1=1
           
            AND   usg.date_id >=  cast(to_char(DATEADD(day,-29, DATE_TRUNC('day',GETDATE()) ),'yyyymmdd')as integer)
            AND   usg.date_id <   cast(to_char(DATEADD(day,30, DATEADD(day,-29, DATE_TRUNC('day',GETDATE()) ) ),'yyyymmdd')as integer)
        )
    , basedata_score as
    (
      Select scr.district_id, scr.school_id, scr.user_id, scr.date_id, scr.userassignment_id, scr.content_id
      , scr.measure_userscore, scr.measure_maxscore
      from report.fact_content_scores scr
      inner join basedata_usage usg on usg.district_id=scr.district_id
            AND usg.school_id=scr.school_id
            AND usg.user_id=scr.user_id
            AND usg.userassignment_id=scr.userassignment_id
            AND usg.content_id=scr.content_id
      where scr.date_id >=  cast(to_char(DATEADD(day,-29, DATE_TRUNC('day',GETDATE()) ),'yyyymmdd')as integer)
    )
    , basedata as
    (
      Select usg.date_id, usg.sessionid, usg.platform, usg.district_id, usg.school_id, usg.class_id
          ,usg.user_id, usg.role_type, usg.product_id, usg.content_id, usg.assignment_id, usg.userassignment_id
          , usg.login_datetime, usg.content_opened_datetime, usg.content_submitted_datetime
          , usg.content_closed_datetime,
      decode(usg.content_submitted_datetime,null,null,(1.0*scr.measure_userscore)/decode(scr.measure_maxscore ,null ,1 ,0,1,scr.measure_maxscore) ) percentscore
      from basedata_usage usg
      left join basedata_score scr on usg.district_id=scr.district_id
          AND usg.school_id=scr.school_id
            AND usg.user_id=scr.user_id
            AND usg.date_id <= scr.date_id
            AND usg.userassignment_id=scr.userassignment_id
            AND usg.content_id=scr.content_id
    )
       ,distinct_classes as
        (
            Select distinct class_id,cls.classtitle from basedata
            inner join  dw.dimclass cls on basedata.class_id=cls.classid
            where (class_id is not null or class_id<>'')
        )
        ,class_teacher as
        (
              Select distinct class_id,classtitle,teacherid,usr.firstname_decrypted teacher_firstname,usr.lastname_decrypted teacher_lastname
              from(
                Select  cls.class_id,classtitle,enr.user_id teacherid
                from distinct_classes cls
                inner join report.dim_class_teacher_enrollment enr on enr.class_id=cls.class_id
                  and enr.is_latest is true
                )qry
              inner join dw.dimuser usr on qry.teacherid=usr.userid
        )
            Select  basedata.sessionid,usr.firstname_decrypted firstname , usr.lastname_decrypted lastname
              , usr.loginname_decrypted loginname, usr.email_decrypted email
              ,org.organizationname school,basedata.role_type role
              ,basedata.product_id,isnull(prd.rumbaproducttitle,basedata.product_id)product_name
              ,isnull(prd.program,basedata.product_id)program
              ,basedata.class_id,ct.classtitle,ct.teacher_firstname,ct.teacher_lastname
              ,cont.contenttitle,cont.contentType
              ,basedata.login_datetime logintime, basedata.content_opened_datetime attemptedtime
              ,basedata.content_closed_datetime closedtime,basedata.content_submitted_datetime submittime,basedata.percentscore
            from basedata
            left outer join nexgen.dimrumbaproduct prd on basedata.product_id=prd.rumbaproductid
           --left outer join dw.dimclass cls on basedata.class_id=cls.classid
            inner join dw.dimcontent cont on basedata.content_id=cont.contentassetid
            left outer join class_teacher  ct on basedata.class_id=ct.class_id
            inner join dw.dimuser usr on basedata.user_id=usr.userid
            inner join dw.dimorganization org on basedata.school_id=org.organizationid
          Group by basedata.sessionid,usr.firstname_decrypted  , usr.lastname_decrypted
              , usr.loginname_decrypted , usr.email_decrypted
              ,org.organizationname ,basedata.role_type
              ,basedata.product_id,product_name
              ,program
              ,basedata.class_id,ct.classtitle,ct.teacher_firstname,ct.teacher_lastname
              ,cont.contenttitle,cont.contentType
              ,basedata.login_datetime, basedata.content_opened_datetime
              ,basedata.content_closed_datetime,basedata.content_submitted_datetime,basedata.percentscore
             )
SELECT 
	TO_CHAR(l_content_usage.logintime , 'YYYY-MM-DD HH24:MI:SS') AS "l_content_usage.login_time",
	l_content_usage.role  AS "l_content_usage.role",
	l_content_usage.loginname  AS "l_content_usage.login_name",
	l_content_usage.firstname  AS "l_content_usage.first_name",
	l_content_usage.lastname  AS "l_content_usage.last_name",
	l_content_usage.email  AS "l_content_usage.email",
	l_content_usage.school  AS "l_content_usage.school",
	l_content_usage.classtitle  AS "l_content_usage.class",
	l_content_usage.teacher_firstname  AS "l_content_usage.teacher_first_name",
	l_content_usage.teacher_lastname  AS "l_content_usage.teacher_last_name",
	l_content_usage.program  AS "l_content_usage.program",
	l_content_usage.product_name  AS "l_content_usage.product",
	l_content_usage.contenttitle  AS "l_content_usage.content_name",
	l_content_usage.contenttype  AS "l_content_usage.type",
	TO_CHAR(l_content_usage.attemptedtime , 'YYYY-MM-DD HH24:MI:SS') AS "l_content_usage.content_open_time",
	TO_CHAR(l_content_usage.submittime , 'YYYY-MM-DD HH24:MI:SS') AS "l_content_usage.content_submit_time",
	TO_CHAR(l_content_usage.closedtime , 'YYYY-MM-DD HH24:MI:SS') AS "l_content_usage.content_closed_time",
	AVG(l_content_usage.percentscore ) AS "l_content_usage.percent_score"
FROM l_content_usage

GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
ORDER BY 1 DESC
LIMIT 5000