1. With qry as(
2. (Select standard_id,user_id,subject
3.         ,round(sum(measure_userscore)*100/sum(measure_maxscore),2)percent_Score from
4.         (SELECT  user_id,question_id, assessment_id,prd.discipline subject, product_id
5.         ,start_school_year,end_school_year,prd.rumbaproducttitle product_name
6.         ,district_id, school_id,class_id,standard_id,framework_id,measure_userscore,measure_maxscore                                        
7. FROM report.v_standard_scores fact
8.                  INNER JOIN dw.dimcontent cont on fact.assessment_id=cont.contentassetid    
9.                  INNER JOIN report.dim_date dd ON fact.date_id = dd.date_id    
10.                 INNER JOIN  report.standard_hierarchy stdhier on fact.question_id =stdhier.content_id  
11.                  INNER JOIN nexgen.dimrumbaproduct prd on fact.product_id=prd.rumbaproductid
12.         Where fact.district_id in  ({{ _user_attributes['subscription_list'] }})
13.                   and  cont.includeinmastery = 'yes'
14.                   and {% condition event_date %} dd.date_value {% endcondition %}
15.                   and fact.userassignment_id <>''
16.                 group by  user_id,question_id, assessment_id,prd.discipline,start_school_year,end_school_year
17.                 ,product_id,product_name,district_id,school_id,class_id,standard_id
18.                 ,framework_id,measure_userscore,measure_maxscore        
19.         )
20.         group by standard_id,subject,user_id
21. ),
22. school_years as (
23.   SELECT MIN(school_year) as min_school_year, MAX(school_year) as max_school_year
24.   FROM report.dim_date
25.   WHERE {% condition event_date %} date_value {% endcondition %}
26. ),
27. standards as  (
28. Select standard_id, subject, avg(percent_Score) avg_score
29.   from qry
30.   cross join school_years
31. where
32.      start_School_Year <= school_years.max_school_year
33.      and isnull(end_school_year, 2099) > school_years.min_school_year
34. group by standard_id,subject
35. )
36. select * from subject
37. )
38.