WITH l_standards_scores_by_student_in_context_v AS (Select student_id,teacher_id ,student.fullname_decrypted as student_name ,teacher.fullname_decrypted as teacher_name ,student.loginname_decrypted as student_loginname ,student.sis_sourceid as student_sis_sourceid ,student.sis_user_identifier as student_sis_user_identifier ,assessment_id, product_id,district_id, school_id ,org.organizationname as school_name ,class_id,cl.classtitle as class_name ,standard_id,framework_id, fw.frameworkname as standards_set_name ,std.description as standard_description, std.code as standard_code , prod.rumbaproducttitle as product_name, prod.program as product_family ,sum_usr_score,sum_max_score,score_perc from ( Select user_id student_id,district_id, school_id ,content_id, assessment_id, product_id,class_id ,standard_id,framework_id ,enr.userid teacher_id,sum_usr_score,sum_max_score,score_perc ,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 from ( SELECT fact.user_id,fact.district_id, fact.school_id ,fact.content_id, fact.assessment_id, fact.product_id ,fact.class_id,std_h.standard_id, std_h.framework_id ,sum(fact.measure_userscore) as sum_usr_score, sum(fact.measure_maxscore) as sum_max_score ,(sum(fact.measure_userscore) / sum(fact.measure_maxscore)) * 100 as score_perc FROM report.v_standard_scores fact INNER JOIN dw.dimcontent cont on fact.assessment_id=cont.contentassetid INNER JOIN report.standard_hierarchy std_h ON fact.content_id = std_h.content_id INNER JOIN report.dim_date dd ON fact.date_id = dd.date_id Where fact.district_id in ('8a97b09e3abc60e2013ae60bd43e0073') and cont.includeinmastery = 'yes' and ((( dd.date_value ) >= (TIMESTAMP '2018-10-01') AND ( dd.date_value ) < (TIMESTAMP '2018-10-31'))) GROUP BY user_id,district_id, school_id ,fact.content_id, fact.assessment_id, fact.product_id, fact.class_id ,std_h.standard_id, std_h.framework_id ) inner_qry INNER JOIN dw.dimuserenrollment enr ON inner_qry.class_id = enr.enrolledtoid AND upper(enr.roletype)='TEACHER' ) outer_qry INNER JOIN dw.dimframework fw ON outer_qry.framework_id = fw.frameworkid INNER JOIN dw.dimuser teacher ON outer_qry.teacher_id=teacher.userid INNER JOIN dw.dimuser student ON outer_qry.student_id=student.userid INNER JOIN dw.dimorganization org ON outer_qry.school_id =org.organizationid INNER JOIN dw.dimstandard std ON outer_qry.standard_id =std.standardid INNER JOIN dw.dimclass cl ON outer_qry.class_id=cl.classid INNER JOIN nexgen.dimrumbaproduct prod ON prod.rumbaproductid = outer_qry.product_id where rnk=1 ) SELECT * FROM ((SELECT l_standards_scores_by_student_in_context_v.school_name AS "l_standards_scores_by_student_in_context_v.school_name", '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", l_standards_scores_by_student_in_context_v.student_name AS "l_standards_scores_by_student_in_context_v.student_name", l_standards_scores_by_student_in_context_v.student_id AS "l_standards_scores_by_student_in_context_v.student_id", 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", 0 AS z___grouping_0, 0 AS z___grouping_1, 0 AS z___grouping_2, 0 AS z___grouping_3, 0 AS z___grouping_4, 0 AS z___grouping, AVG(l_standards_scores_by_student_in_context_v.score_perc ) AS "l_standards_scores_by_student_in_context_v.average_score" FROM l_standards_scores_by_student_in_context_v WHERE (l_standards_scores_by_student_in_context_v.district_id = '8a97b09e3abc60e2013ae60bd43e0073') GROUP BY 1, 2, 3, 4, 5) UNION ALL (SELECT l_standards_scores_by_student_in_context_v.school_name AS "l_standards_scores_by_student_in_context_v.school_name", '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", l_standards_scores_by_student_in_context_v.student_name AS "l_standards_scores_by_student_in_context_v.student_name", l_standards_scores_by_student_in_context_v.student_id AS "l_standards_scores_by_student_in_context_v.student_id", NULL, 0 AS z___grouping_0, 0 AS z___grouping_1, 0 AS z___grouping_2, 0 AS z___grouping_3, 1 AS z___grouping_4, 1 AS z___grouping, AVG(l_standards_scores_by_student_in_context_v.score_perc ) AS "l_standards_scores_by_student_in_context_v.average_score" FROM l_standards_scores_by_student_in_context_v WHERE (l_standards_scores_by_student_in_context_v.district_id = '8a97b09e3abc60e2013ae60bd43e0073') GROUP BY 1, 2, 3, 4) UNION ALL (SELECT l_standards_scores_by_student_in_context_v.school_name AS "l_standards_scores_by_student_in_context_v.school_name", '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", NULL, NULL, NULL, 0 AS z___grouping_0, 0 AS z___grouping_1, 1 AS z___grouping_2, 1 AS z___grouping_3, 1 AS z___grouping_4, 3 AS z___grouping, AVG(l_standards_scores_by_student_in_context_v.score_perc ) AS "l_standards_scores_by_student_in_context_v.average_score" FROM l_standards_scores_by_student_in_context_v WHERE (l_standards_scores_by_student_in_context_v.district_id = '8a97b09e3abc60e2013ae60bd43e0073') GROUP BY 1, 2) UNION ALL (SELECT l_standards_scores_by_student_in_context_v.school_name AS "l_standards_scores_by_student_in_context_v.school_name", NULL, NULL, NULL, NULL, 0 AS z___grouping_0, 1 AS z___grouping_1, 1 AS z___grouping_2, 1 AS z___grouping_3, 1 AS z___grouping_4, 4 AS z___grouping, AVG(l_standards_scores_by_student_in_context_v.score_perc ) AS "l_standards_scores_by_student_in_context_v.average_score" FROM l_standards_scores_by_student_in_context_v WHERE (l_standards_scores_by_student_in_context_v.district_id = '8a97b09e3abc60e2013ae60bd43e0073') GROUP BY 1)) ORDER BY z___grouping_0,1 ,z___grouping_1,2 ,z___grouping_2,3 ,z___grouping_3,4 ,12 DESC,z___grouping_4,5 LIMIT 20000