Zadanie w ms sql - jak zrobić?

with ctes as (
select 
    pesel
from (select 
          * 
          ,row_number() over (partition by pesel order by data_skier) r
      from 
          wizyty) w
      left join (values 
                     (1,'Pediatra')
                     ,(2,'Gastrolog')
                     ,(3,'Ginekolog')
                     ,(4,'Internista')
                ) as S(r,l) on s.r=w.r and s.l=w.lekarz
group by 
    pesel
having 
    count(s.r)=4
 )
 
 
 select 
  'Pediatra->Gastrolog->Ginekolog->Interista' as Sekwencja
  ,count(distinct pesel) IloscPacjentów
from ctes

with ctes as (
select
    s.*
    ,w.pesel
    ,w.r wr
from 
    s 
    left join (select 
          * 
          ,row_number() over (partition by pesel order by data_skier) r
      from 
          wizyty) w
     on s.r=w.r and s.lekarz=w.lekarz )
 , cteAgg as ( 
select 
    id
    ,pesel 
from 
    ctes
group by 
    id
    ,pesel
 having 
    count(wr)=max(ilosc)
 )
 
 select id, count(distinct pesel) IlePacjentow from cteagg
 group by id