What is a CTE (Common Table Expression)? What are its uses?

Architectural Logic: A CTE is a named temporary result set (WITH ... AS) scoped to a single statement. Why use CTEs: Readability, modular logic, recursion (hierarchies), and avoiding repeated subquery evaluation in some engines. Scalability: CTEs may be inlined or materialized—behavior varies by engine. In BigQuery/Snowflake, CTEs are typically not materialized unless hinted; repeated references can re-execute. Cost: Materialized CTEs (e.g., WITH ... MATERIALIZED) increase memory/disk but avoid recomputation; use when referenced multiple times. Example: WITH monthly_sales AS (SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS total FROM orders GROUP BY 1), growth AS (SELECT month, total, (total - LAG(total) OVER (ORDER BY month)) / NULLIF(LAG(total) OVER (ORDER BY month), 0) 100 AS pct_growth FROM monthly_sales) SELECT FROM growth WHERE pct_growth > 10.