Decomposing a large query into smaller subsets is the right approach, but I would strongly suggest doing it with VIEWs rather than CTEs. CTEs are a useful tool,

Decomposing a large query into smaller subsets is the right approach, but I would strongly suggest doing it with VIEWs rather than CTEs. CTEs are a useful tool, but come with their own performance profiles and often using them too much will lead to slower queries.