Problem with cte (executing slowly)

;with cte as 
 (SELECT 
    
    acad_period, student_id, student_name, student_surname, stage_code, campus_desc, dept_code, dept_desc, 
	c_title, c_initial, c_surname, course_code, course_period, course_desc, register_id, register_group,
	module_code, module_period, module_desc, day_num, day, week_no, start_time, end_time, 
	date as 'date_of_first_absence', absence_code, moa,

	ROW_NUMBER() OVER (PARTITION BY register_id, register_group, student_id 
        ORDER BY date,start_time) AS rownum
FROM eRegisters_allstudents_tb

where
stage_code like 'ENR'

)

, XX as (select a.register_id,a.register_group,a.student_id,a.rownum,count(*) as Missed
          From Cte as a
          Inner join cte as B
            on a.rownum<=b.rownum
           and a.register_id=b.register_id
           and a.register_group=b.register_group
           and a.student_id=b.student_id
           and a.absence_code=b.absence_code
          Where A.absence_code='O'
           and not exists (select x.student_id from cte as x
                            where a.student_id=x.student_id
                             and a.register_id=x.register_id
                             and a.register_group=x.register_group
                             and x.rownum between a.rownum and b.rownum
                             and ( (x.absence_code <> 'O') or (x.absence_code is NULL))  )
            group by a.register_id,a.register_group,a.student_id,a.rownum
            having count(*) >= 2
         )

, yy as (select a.register_id,a.register_group,a.student_id,a.rownum,a.missed
          From xx as a
          Where not exists (select x.student_id from xx as x
                              where a.student_id=x.student_id  
                                and a.register_id=x.register_id
                                and a.register_group=x.register_group
                                and X.rownum < a.rownum  
                                and a.rownum <=x.rownum + x.missed) 
         )

Select A.*,b.missed 

  from Cte as A
  Inner Join YY as b
   on a.register_id=b.register_id
   and a.register_group=b.register_group
   and a.student_id=b.student_id
   and a.rownum=b.rownum

Select allOpen in new window