What is the difference between a subquery and a Common Table Expression (CTE)? When would you prefer one over the other in an analytics query?

Sample answer preview

Subqueries and Common Table Expressions are both ways to break complex queries into logical parts, but they differ in syntax, readability, and reusability within a query. A subquery is a query nested inside another query.