With qry as(
(Select standard_id,user_id,subject
        ,round(sum(measure_userscore)*100/sum(measure_maxscore),2)percent_Score from
        (SELECT  user_id,question_id, assessment_id,prd.discipline subject, product_id
        ,start_school_year,end_school_year,prd.rumbaproducttitle product_name
        ,district_id, school_id,class_id,standard_id,framework_id,measure_userscore,measure_maxscore                                        
FROM report.v_standard_scores fact 
                 INNER JOIN dw.dimcontent cont on fact.assessment_id=cont.contentassetid    
                 INNER JOIN report.dim_date dd ON fact.date_id = dd.date_id    
                INNER JOIN  report.standard_hierarchy stdhier on fact.question_id =stdhier.content_id  
                 INNER JOIN nexgen.dimrumbaproduct prd on fact.product_id=prd.rumbaproductid
        Where fact.district_id in  ({{ _user_attributes['subscription_list'] }})
                  and  cont.includeinmastery = 'yes' 
                  and {% condition event_date %} dd.date_value {% endcondition %}
                  and fact.userassignment_id <>''
                group by  user_id,question_id, assessment_id,prd.discipline,start_school_year,end_school_year
                ,product_id,product_name,district_id,school_id,class_id,standard_id
                ,framework_id,measure_userscore,measure_maxscore        
        )
        group by standard_id,subject,user_id
),
school_years as (
  SELECT MIN(school_year) as min_school_year, MAX(school_year) as max_school_year
  FROM report.dim_date
  WHERE {% condition event_date %} date_value {% endcondition %}
),
standards as  (
Select standard_id, subject, avg(percent_Score) avg_score
  from qry 
  cross join school_years
where 
     start_School_Year <= school_years.max_school_year
     and isnull(end_school_year, 2099) > school_years.min_school_year
group by standard_id,subject
)
select * from subject
)