Slow row count and self join on common table expression result set [closed]

I have a fairly complex SQL, which I have broken into 4 consecutive common table expressions(CTE). The first 3 expressions do the heavy lifting and contain most of the logic. A total of ~6000 records are returned after the third CTE and the average execution time is ~ 500 ms which is descent in my […]