I don't see any reference to cte1. Is that expected?
I'm unclear why these sets are not just inner join'd on resource_surrogate_id. It seems like that column it is being selected as Sid1 in each CTE, and then the next one does the below. Why?
where resource_surrogate_id IN (SELECT Sid1 FROM cte_previous_number)