How Can I set the right value according to comparison conditition

Question

Hello,

i have 2 tables , the first one is a referential of the quality services and the second is a historical tables as bellow the DDL :

drop table if exists #oderstatus_quality
 create table #oderstatus_quality (
	 id int identity primary key,
	 label varchar(100),
	 orderstatus_src int,
	 orderstatus_src_label varchar(100),
	 orderstatus_tgt int,
	 orderstatus_tgt_label varchar(100)
 )
insert into #oderstatus_quality values
 ('refunded vs Ordered',36,'Ordered',37,'refunded')
,('Cancelled vs Ordered',36,'Ordered',35,'Cancelled')
,('received vs Ordered',36,'Ordered',9,'received')
,('navigator Closed vs Cancelled',4,'Cancelled',11,'navigator Closed')
,('Banking Authorization refused vs Cancelled',4,'Cancelled',13,'Banking Authorization refused')
drop table if exists #ordersStatusHistory
Create table #ordersStatusHistory(
	 id int identity primary key,
	 order_number varchar(255),
	 order_status int,
	 order_date datetime
)
insert into #ordersStatusHistory 
values
 ('XN75',36,'2022-01-27 15:29:22.000')
,('XN75',9,'2022-02-01 07:15:53.000')
,('XN75',37,'2022-02-03 07:50:29.000')
,('XN76',36,'2022-03-01 07:15:53.000')
,('XN76',9,'2022-03-05 07:15:53.000')
,('XN77',36,'2022-02-03 07:15:53.000')
,('XN77',36,'2022-02-03 08:15:53.000')
,('XN77',9,'2022-02-04 17:30:00.000')
,('XN78',36,'2022-02-05 07:15:53.000')
,('XN78',4,'2022-02-05 07:20:53.000')
,('XN79',4,'2022-02-06 07:20:53.000')
,('XN79',11,'2022-02-06 07:21:53.000')
,('XN80',36,'2022-04-06 09:15:53.000')
,('XN80',4,'2022-04-06 09:20:53.000')
,('XN80',11,'2022-04-06 09:21:53.000')

For the reporting purpose , i want to calculate the quality of my delivery for that i need two measures Comparaison_Status and All_Status

the comparaison status it's the number of status 1 vs status 2 for example i have 'refunded vs Ordered' That mean i want to calcuate the number of orders that was orded but also was refunded

The All_Status , it's the number of the status 2 for example i have 'refunded vs Ordered' that mean i want to see only the the orders that was ordered

I tried the query as bellow , it's give me the right result but if someone have another solution and better for performance because my table is huge arround 30 millions rows , it will better

;with src as (
select *,ROW_NUMBER() over(partition by order_number,order_status order by order_date asc) as grp from #ordersStatusHistory o
where o.order_status in(select distinct orderstatus_src from #oderstatus_quality)
)
,tgt as(
select *,ROW_NUMBER() over(partition by order_number,order_status order by order_date asc) as grp from #ordersStatusHistory o
where o.order_status in(select distinct orderstatus_tgt from #oderstatus_quality)
)
,numberofAllstatus as(
select *, 1 AS AllValues from src
)

	select
	  s.order_number
	  ,s.order_date
	  ,q.label
	  ,count(s.order_status)    as Comparaison_Status
	  ,max(s.AllValues) as All_Status
	from numberofAllstatus s
	inner join tgt t
	on s.order_number = t.order_number and s.grp = t.grp
	inner join #oderstatus_quality q 
	on s.order_status =q.orderstatus_src and t.order_status = q.orderstatus_tgt
	group by 
		s.order_number
	   ,s.order_date
	   ,q.label
   union 
   select 
       s.order_number
	  ,s.order_date
	  ,q.label 
	    ,0   as Comparaison_Status
	  ,max(s.AllValues) as All_Status
	from #oderstatus_quality q
	left join numberofAllstatus s
	on s.order_status =q.orderstatus_src 
	left join  tgt t
	on s.order_number = t.order_number and s.grp = t.grp and t.order_status = q.orderstatus_tgt
	where 
	1=1
	--and s.order_number='XN75'
	and t.id is null
	group by 
		s.order_number
	   ,s.order_date
	   ,q.label

the expected result that i want :

Thanks for help !