1. WITH l_standards_scores_by_student_in_context_v AS (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 framework_id,score_perc,class_id,district_id,student_id,school_id,content_id,assessment_id,product_id,standard_id ,sum_usr_score,sum_max_score 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  ('8a97b09e3abc60e2013ae60bd43e0073')
31.                                        and  cont.includeinmastery = 'yes'
32.                                        and ((( dd.date_value ) >= (TIMESTAMP '2018-10-01') AND ( dd.date_value ) < (TIMESTAMP '2018-10-31')))
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.              )
48. SELECT * FROM ((SELECT
49.         l_standards_scores_by_student_in_context_v.school_name  AS "l_standards_scores_by_student_in_context_v.school_name",
50.         'Class: ' || l_standards_scores_by_student_in_context_v.class_name || '|' || 'Teacher: ' || l_standards_scores_by_student_in_context_v.teacher_name AS "l_standards_scores_by_student_in_context_v.class_teacher_name",
51.         l_standards_scores_by_student_in_context_v.student_name  AS "l_standards_scores_by_student_in_context_v.student_name",
52.         l_standards_scores_by_student_in_context_v.student_id  AS "l_standards_scores_by_student_in_context_v.student_id",
53.         l_standards_scores_by_student_in_context_v.standard_code || ' - ' || l_standards_scores_by_student_in_context_v.standard_description  AS "l_standards_scores_by_student_in_context_v.standard_code_and_description",
54.         0 AS z___grouping_0,
55.         0 AS z___grouping_1,
56.         0 AS z___grouping_2,
57.         0 AS z___grouping_3,
58.         0 AS z___grouping_4,
59.         0 AS z___grouping,
60.         AVG(l_standards_scores_by_student_in_context_v.score_perc ) AS "l_standards_scores_by_student_in_context_v.average_score"
61. FROM l_standards_scores_by_student_in_context_v
62.  
63. WHERE (l_standards_scores_by_student_in_context_v.district_id = '8a97b09e3abc60e2013ae60bd43e0073') GROUP BY 1, 2, 3, 4, 5) UNION ALL (SELECT
64.         l_standards_scores_by_student_in_context_v.school_name  AS "l_standards_scores_by_student_in_context_v.school_name",
65.         'Class: ' || l_standards_scores_by_student_in_context_v.class_name || '|' || 'Teacher: ' || l_standards_scores_by_student_in_context_v.teacher_name AS "l_standards_scores_by_student_in_context_v.class_teacher_name",
66.         l_standards_scores_by_student_in_context_v.student_name  AS "l_standards_scores_by_student_in_context_v.student_name",
67.         l_standards_scores_by_student_in_context_v.student_id  AS "l_standards_scores_by_student_in_context_v.student_id",
68.         NULL,
69.         0 AS z___grouping_0,
70.         0 AS z___grouping_1,
71.         0 AS z___grouping_2,
72.         0 AS z___grouping_3,
73.         1 AS z___grouping_4,
74.         1 AS z___grouping,
75.         AVG(l_standards_scores_by_student_in_context_v.score_perc ) AS "l_standards_scores_by_student_in_context_v.average_score"
76. FROM l_standards_scores_by_student_in_context_v
77.  
78. WHERE (l_standards_scores_by_student_in_context_v.district_id = '8a97b09e3abc60e2013ae60bd43e0073') GROUP BY 1, 2, 3, 4) UNION ALL (SELECT
79.         l_standards_scores_by_student_in_context_v.school_name  AS "l_standards_scores_by_student_in_context_v.school_name",
80.         'Class: ' || l_standards_scores_by_student_in_context_v.class_name || '|' || 'Teacher: ' || l_standards_scores_by_student_in_context_v.teacher_name AS "l_standards_scores_by_student_in_context_v.class_teacher_name",
81.         NULL,
82.         NULL,
83.         NULL,
84.         0 AS z___grouping_0,
85.         0 AS z___grouping_1,
86.         1 AS z___grouping_2,
87.         1 AS z___grouping_3,
88.         1 AS z___grouping_4,
89.         3 AS z___grouping,
90.         AVG(l_standards_scores_by_student_in_context_v.score_perc ) AS "l_standards_scores_by_student_in_context_v.average_score"
91. FROM l_standards_scores_by_student_in_context_v
92.  
93. WHERE (l_standards_scores_by_student_in_context_v.district_id = '8a97b09e3abc60e2013ae60bd43e0073') GROUP BY 1, 2) UNION ALL (SELECT
94.         l_standards_scores_by_student_in_context_v.school_name  AS "l_standards_scores_by_student_in_context_v.school_name",
95.         NULL,
96.         NULL,
97.         NULL,
98.         NULL,
99.         0 AS z___grouping_0,
100.         1 AS z___grouping_1,
101.         1 AS z___grouping_2,
102.         1 AS z___grouping_3,
103.         1 AS z___grouping_4,
104.         4 AS z___grouping,
105.         AVG(l_standards_scores_by_student_in_context_v.score_perc ) AS "l_standards_scores_by_student_in_context_v.average_score"
106. FROM l_standards_scores_by_student_in_context_v
107.  
108. WHERE (l_standards_scores_by_student_in_context_v.district_id = '8a97b09e3abc60e2013ae60bd43e0073') GROUP BY 1))
109. ORDER BY z___grouping_0,1 ,z___grouping_1,2 ,z___grouping_2,3 ,z___grouping_3,4 ,12 DESC,z___grouping_4,5
110. LIMIT 20000