CTEs, Subqueries, and Query Optimisation in SQL

If you have been writing SQL for a while, you have almost certainly run into a moment where a query starts to feel unwieldy. You need to filter based on an aggregation, or you need to reference a result you just computed, and suddenly your query is nested three levels deep and impossible to read. That is where CTEs and subqueries come in. Both solve the same core problem: referencing intermediate results within a larger query. But they do it differently, and knowing when to reach for each one wi