"select count(1)" performance in a CTE Given the below CTE:

with seekResults as
(
select DISTINCT
 trx_id_fk,
 account_FK, 
 customer_cif,
 customer_tin,
 TIN_Type, 
 ID_NUMBER ,
 SEARCH_NUMBER
 FROM transactionsOP 
) 

 select trx_id_fk 
  ,case when account_FK is not null then (select count(1) from seekResults a where a.account_fk = s.account_fk and a.SEARCH_NUMBER= s.SEARCH_NUMBER) 
   when customer_cif is not null then (select count(1) from seekResults a where a.customer_cif = s.customer_cif and a.SEARCH_NUMBER= s.SEARCH_NUMBER) 
   when customer_tin is not null then (select count(1) from seekResults a where a.customer_tin = s.customer_tin and a.TIN_Type = s.TIN_Type and a.SEARCH_NUM = s.SEARCH_NUM) 
   when ID_NUMBER is not null then (select count(1) from seekResults a where a.ID_NUMBER = s.ID_NUMBER and a.SEARCH_NUMBER= s.SEARCH_NUMBER)
   else (select count(1) from seekResults a where a.SEARCH_NUMBER= s.SEARCH_NUMBER)
       end as 'transaction_period'
from seekResults s  ;


I noticed that it is taking a lot of time doing the calculation because it is calling the seekResults CTE over and over again, mostly in the select count(1) section:

select count(1) from seekResults  a 


Is there a better way to improve that part ? So far I haven't found anything to replace it with.
I think your query can be written using window aggregates. I don't know if it will be more efficient though:

with seekResults as
(
  select distinct
    trx_id_fk,
    account_fk
    customer_cif,
    customer_tin,
    tin_type,
    id_number,
    search_number
  from transactionsOP 
) 
select 
  trx_id_fk,  
  case 
    when account_fk is not null then
      count(*) over (partition by search_number, account_fk) 
    when customer_cif is not null then
      count(*) over (partition by search_number, customer_cif) 
    when customer_tin is not null then
      count(*) over (partition by search_number, customer_tin, tin_type)
    when id_number is not null then
      count(*) over (partition by search_number, id_number)
    else 
      count(*) over (partition by search_number)
  end as transaction_period
from seekResults ;