1. WITH l_standards_scores_by_student_by_standard_v AS (With qry as(
2.       Select class_id,user_id student_id,start_school_year,end_school_year, discipline, product_name
3.         ,product_id,school_id,standards_set_name,standard_code
4.         ,avg(percent_Score) avg_score from
5.         (Select standard_id,class_id,user_id,start_school_year,end_school_year
6.         ,discipline, product_name, product_id,school_id,standards_set_name,standard_code
7.         ,sum(measure_userscore)/sum(measure_maxscore) percent_Score from
8.         (SELECT  user_id,question_id, assessment_id,prd.discipline, product_id,prd.rumbaproducttitle product_name,fw.frameworkname as standards_set_name
9.         , std.code as standard_code,district_id, start_school_year,end_school_year, school_id,class_id,standard_id,framework_id,measure_userscore,measure_maxscore
10.         FROM report.fact_standard_scores fact
11.         INNER JOIN dw.dimcontent  AS dimcontent ON fact.assessment_id =  dimcontent.contentassetid
12.         INNER JOIN  report.standard_hierarchy stdhier on fact.question_id =stdhier.content_id
13.         INNER JOIN nexgen.dimrumbaproduct prd on fact.product_id=prd.rumbaproductid
14.         INNER JOIN report.dim_date as calendar on fact.date_id = calendar.date_id
15.         INNER JOIN dw.dimframework fw ON stdhier.framework_id = fw.frameworkid
16.         INNER JOIN dw.dimstandard std ON stdhier.standard_id = std.standardid
17.         Where fact.district_id in ('8a97b09e3abc60e2013ae60bd43e0073','Rlz_RRSQA_NR_Data_Dist_Org001','Rlz_RRSQA_NR_Data_Dist_Org002','Rlz_RRSQA_NR_Data_Dist_Org003','Rlz_RRSQA_NR_Data_Dist_Org004','Rlz_RRSQA_NR_Data_Dist_Org005','Rlz_RRSQA_NR_Data_Dist_Org006','Rlz_RRSQA_NR_Data_Dist_Org007','Rlz_RRSQA_NR_Data_Dist_Org008','Rlz_RRSQA_NR_Data_Dist_Org009','Rlz_RRSQA_NR_Data_Dist_Org010','Rlz_RRSQA_NR_Data_Dist_Org011','Rlz_RRSQA_NR_Data_Dist_Org012','Rlz_RRSQA_NR_Data_Dist_Org013')
18.         and ((( calendar.date_value ) >= ((DATEADD(month,-5, DATE_TRUNC('month', DATE_TRUNC('day',GETDATE())) ))) AND ( calendar.date_value ) < ((DATEADD(month,6, DATEADD(month,-5, DATE_TRUNC('month', DATE_TRUNC('day',GETDATE())) ) )))))
19.         and 1=1 -- no filter on 'l_standards_scores_by_student_by_standard_v.assessment_filter'
20.  
21.         and 1=1 -- no filter on 'l_standards_scores_by_student_by_standard_v.subject_filter'
22.  
23.         and fact.userassignment_id <>''
24.         group by  user_id,question_id, assessment_id,prd.discipline, product_id,product_name,district_id,fw.frameworkname,std.code
25.         ,school_id,class_id,standard_id,framework_id,measure_userscore,measure_maxscore,start_school_year,end_school_year
26.         )
27.         group by standard_id,class_id,user_id,measure_maxscore,start_school_year,end_school_year,discipline, product_name
28.         ,product_id,school_id,standards_set_name,standard_code
29.         )
30.         group by user_id,class_id,start_school_year,end_school_year,discipline, product_name, product_id,school_id,standards_set_name,standard_code
31.   ),
32.   school_years as (
33.     SELECT MIN(school_year) as min_school_year, MAX(school_year) as max_school_year
34.     FROM report.dim_date
35.     WHERE ((( date_value ) >= ((DATEADD(month,-5, DATE_TRUNC('month', DATE_TRUNC('day',GETDATE())) ))) AND ( date_value ) < ((DATEADD(month,6, DATEADD(month,-5, DATE_TRUNC('month', DATE_TRUNC('day',GETDATE())) ) )))))
36.   ),
37.   class_qry as (
38.   Select qry.class_id, qry.school_id, cls.classtitle class_name, enr.user_id teacher_id, qry.student_id, qry.avg_score
39.   ,qry.start_school_year,qry.end_school_year, qry.discipline, qry.product_name, qry.product_id,qry.standards_set_name,qry.standard_code
40.     ,row_number()over(partition by qry.class_id order by enr.scdtimestamp desc ) rownum
41.     from qry
42.     INNER JOIN dw.dimclass cls
43.     ON qry.class_id = cls.classid and isdeleted is false
44.     INNER JOIN report.dim_class_teacher_enrollment enr ON qry.class_id = enr.class_id
45.   ),
46.   standards_score as (
47.   Select q1.class_id, q1.school_id, q1.class_name, q1.student_id
48.   ,stu.fullname_decrypted as student_name, q1.teacher_id
49.   ,usr.fullname_decrypted as teacher_name,q1.standards_set_name,q1.standard_code
50.   ,q1.avg_score  from class_qry q1
51.   inner join dw.dimuser usr on usr.userid=q1.teacher_id
52.   inner join dw.dimuser stu on stu.userid=q1.student_id
53.   cross join school_years sch
54.   where
55.        q1.start_school_year <= sch.max_school_year
56.        and isnull(q1.end_school_year, 2099) > sch.min_school_year
57.   )
58.   select class_id, school_id, standards_set_name, standard_code, class_name, student_id, student_name, teacher_id, teacher_name, avg_score from standards_score
59.        )
60. SELECT
61.         l_standards_scores_by_student_by_standard_v.student_name  AS "l_standards_scores_by_student_by_standard_v.student_name",
62.         l_standards_scores_by_student_by_standard_v.student_id  AS "l_standards_scores_by_student_by_standard_v.student_id",
63.         l_standards_scores_by_student_by_standard_v.class_name  AS "l_standards_scores_by_student_by_standard_v.class_name",
64.         l_standards_scores_by_student_by_standard_v.teacher_name  AS "l_standards_scores_by_student_by_standard_v.teacher_name",
65.         l_standards_scores_by_student_by_standard_v.teacher_id  AS "l_standards_scores_by_student_by_standard_v.teacher_id",
66.         AVG(l_standards_scores_by_student_by_standard_v.avg_score ) AS "l_standards_scores_by_student_by_standard_v.average_score",
67.         COALESCE(SUM(0 ), 0) AS "l_standards_scores_by_student_by_standard_v.dummy_average_score"
68. FROM l_standards_scores_by_student_by_standard_v
69.  
70. WHERE
71.         (l_standards_scores_by_student_by_standard_v.standards_set_name ILIKE 'Rlz_RRSQA_NR_Data_FWName Name1')
72. GROUP BY 1,2,3,4,5
73. ORDER BY 6 DESC
74. LIMIT 5000