Let's use a classic analogy to understand the motivation for adding indexes.
Build a website with a modern front end & content management stack.
What are the implications of using a CTE instead of a subquery?
SQL works better when you write what rather than how. Describe the data you want. Let the engine decide how to get it.