WITH l_standards_scores_by_student_by_standard_v AS (With qry as(
      Select class_id,user_id student_id,start_school_year,end_school_year, discipline, product_name
        ,product_id,school_id,standards_set_name,standard_code
        ,avg(percent_Score) avg_score from
        (Select standard_id,class_id,user_id,start_school_year,end_school_year
        ,discipline, product_name, product_id,school_id,standards_set_name,standard_code
        ,sum(measure_userscore)/sum(measure_maxscore) percent_Score from
        (SELECT  user_id,question_id, assessment_id,prd.discipline, product_id,prd.rumbaproducttitle product_name,fw.frameworkname as standards_set_name
        , 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
        FROM report.fact_standard_scores fact
        INNER JOIN dw.dimcontent  AS dimcontent ON fact.assessment_id =  dimcontent.contentassetid
        INNER JOIN  report.standard_hierarchy stdhier on fact.question_id =stdhier.content_id
        INNER JOIN nexgen.dimrumbaproduct prd on fact.product_id=prd.rumbaproductid
        INNER JOIN report.dim_date as calendar on fact.date_id = calendar.date_id
        INNER JOIN dw.dimframework fw ON stdhier.framework_id = fw.frameworkid
        INNER JOIN dw.dimstandard std ON stdhier.standard_id = std.standardid
        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')
        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())) ) )))))
        and 1=1 -- no filter on 'l_standards_scores_by_student_by_standard_v.assessment_filter' 

        and 1=1 -- no filter on 'l_standards_scores_by_student_by_standard_v.subject_filter' 

        and fact.userassignment_id <>''
        group by  user_id,question_id, assessment_id,prd.discipline, product_id,product_name,district_id,fw.frameworkname,std.code
        ,school_id,class_id,standard_id,framework_id,measure_userscore,measure_maxscore,start_school_year,end_school_year
        )
        group by standard_id,class_id,user_id,measure_maxscore,start_school_year,end_school_year,discipline, product_name
        ,product_id,school_id,standards_set_name,standard_code
        )
        group by user_id,class_id,start_school_year,end_school_year,discipline, product_name, product_id,school_id,standards_set_name,standard_code
  ),
  school_years as (
    SELECT MIN(school_year) as min_school_year, MAX(school_year) as max_school_year
    FROM report.dim_date
    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())) ) )))))
  ),
  class_qry as (
  Select qry.class_id, qry.school_id, cls.classtitle class_name, enr.user_id teacher_id, qry.student_id, qry.avg_score
  ,qry.start_school_year,qry.end_school_year, qry.discipline, qry.product_name, qry.product_id,qry.standards_set_name,qry.standard_code
    ,row_number()over(partition by qry.class_id order by enr.scdtimestamp desc ) rownum
    from qry
    INNER JOIN dw.dimclass cls
    ON qry.class_id = cls.classid and isdeleted is false
    INNER JOIN report.dim_class_teacher_enrollment enr ON qry.class_id = enr.class_id
  ),
  standards_score as (
  Select q1.class_id, q1.school_id, q1.class_name, q1.student_id
  ,stu.fullname_decrypted as student_name, q1.teacher_id
  ,usr.fullname_decrypted as teacher_name,q1.standards_set_name,q1.standard_code
  ,q1.avg_score  from class_qry q1
  inner join dw.dimuser usr on usr.userid=q1.teacher_id
  inner join dw.dimuser stu on stu.userid=q1.student_id
  cross join school_years sch
  where
       q1.start_school_year <= sch.max_school_year
       and isnull(q1.end_school_year, 2099) > sch.min_school_year
  )
  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
       )
SELECT 
	l_standards_scores_by_student_by_standard_v.student_name  AS "l_standards_scores_by_student_by_standard_v.student_name",
	l_standards_scores_by_student_by_standard_v.student_id  AS "l_standards_scores_by_student_by_standard_v.student_id",
	l_standards_scores_by_student_by_standard_v.class_name  AS "l_standards_scores_by_student_by_standard_v.class_name",
	l_standards_scores_by_student_by_standard_v.teacher_name  AS "l_standards_scores_by_student_by_standard_v.teacher_name",
	l_standards_scores_by_student_by_standard_v.teacher_id  AS "l_standards_scores_by_student_by_standard_v.teacher_id",
	AVG(l_standards_scores_by_student_by_standard_v.avg_score ) AS "l_standards_scores_by_student_by_standard_v.average_score",
	COALESCE(SUM(0 ), 0) AS "l_standards_scores_by_student_by_standard_v.dummy_average_score"
FROM l_standards_scores_by_student_by_standard_v

WHERE 
	(l_standards_scores_by_student_by_standard_v.standards_set_name ILIKE 'Rlz_RRSQA_NR_Data_FWName Name1')
GROUP BY 1,2,3,4,5
ORDER BY 6 DESC
LIMIT 5000