1. Select  student_id,teacher_id
2.           ,student.fullname_decrypted  as student_name
3.           ,teacher.fullname_decrypted  as teacher_name
4.           ,student.loginname_decrypted as student_loginname
5.           ,student.sis_sourceid as student_sis_sourceid
6.           ,student.sis_user_identifier as student_sis_user_identifier
7.           ,assessment_id, product_id,district_id, school_id
8.           ,org.organizationname as school_name
9.           ,class_id,cl.classtitle as class_name
10.           ,standard_id,framework_id, fw.frameworkname as standards_set_name
11.           ,std.description as standard_description, std.code as standard_code
12.           , prod.rumbaproducttitle as product_name, prod.program as product_family
13.           ,sum_usr_score,sum_max_score,score_perc
14.               from (
15.               Select user_id student_id,district_id, school_id
16.                 ,content_id, assessment_id, product_id,class_id
17.                 ,standard_id,framework_id
18.                 ,enr.userid teacher_id,sum_usr_score,sum_max_score,score_perc
19.                 ,row_number()over(partition by class_id order by enr.scdtimestamp desc ) rnk
20.               from (
21.                               SELECT fact.user_id,fact.district_id, fact.school_id
22.                                       ,fact.content_id, fact.assessment_id, fact.product_id
23.                                       ,fact.class_id,std_h.standard_id, std_h.framework_id
24.                                       ,sum(fact.measure_userscore) as sum_usr_score, sum(fact.measure_maxscore) as sum_max_score
25.                                       ,(sum(fact.measure_userscore) / sum(fact.measure_maxscore)) * 100 as score_perc
26.                       FROM report.v_standard_scores fact
27.                               INNER JOIN dw.dimcontent cont on fact.assessment_id=cont.contentassetid
28.                               INNER JOIN report.standard_hierarchy std_h ON fact.content_id = std_h.content_id
29.                               INNER JOIN report.dim_date dd ON fact.date_id = dd.date_id
30.                                Where fact.district_id in  ('8a9480bd28df05e40128e3f7f85d1552')
31.                                        and  cont.includeinmastery = 'yes'
32.                                        and ((( dd.date_value ) >= ((DATEADD(month,-2, DATE_TRUNC('month', DATE_TRUNC('day',GETDATE())) ))) AND ( dd.date_value ) < ((DATEADD(month,3, DATEADD(month,-2, DATE_TRUNC('month', DATE_TRUNC('day',GETDATE())) ) )))))
33.                                   GROUP BY user_id,district_id, school_id
34.                                         ,fact.content_id, fact.assessment_id, fact.product_id, fact.class_id
35.                                         ,std_h.standard_id, std_h.framework_id
36.                         ) inner_qry
37.                       INNER JOIN dw.dimuserenrollment enr ON inner_qry.class_id = enr.enrolledtoid AND upper(enr.roletype)='TEACHER'
38.               ) outer_qry
39.               INNER JOIN dw.dimframework fw ON outer_qry.framework_id = fw.frameworkid
40.               INNER JOIN dw.dimuser teacher ON outer_qry.teacher_id=teacher.userid
41.               INNER JOIN dw.dimuser student ON  outer_qry.student_id=student.userid
42.               INNER JOIN dw.dimorganization org ON  outer_qry.school_id =org.organizationid
43.               INNER JOIN dw.dimstandard std ON outer_qry.standard_id =std.standardid
44.               INNER JOIN dw.dimclass cl  ON outer_qry.class_id=cl.classid
45.               INNER JOIN  nexgen.dimrumbaproduct prod ON prod.rumbaproductid = outer_qry.product_id
46.               where rnk=1
47.               group by student_id,teacher_id, student_name, teacher_name,student_loginname
48.               ,student_sis_sourceid, student_sis_user_identifier,assessment_id, product_id
49.               ,district_id, school_id, school_name,class_id, class_name,standard_id,framework_id
50.               ,standards_set_name,standard_description, standard_code, product_name, product_family
51.               ,sum_usr_score,sum_max_score,score_perc