Formatting CTEs for readable analytics SQL
Common Table Expressions turn long SQL pipelines into steps. That helps readability until the file grows to twelve unnamed blocks and inconsistent indentation. A few formatting habits—plus a dialect-aware beautifier—keep CTE-heavy queries maintainable.
Name every CTE for the step it performs
Prefer active_customers over step3. Names should describe the row set after filters and
joins, not the calendar week you wrote the query.
One clause per line in the outer query
After the WITH block, format the final SELECT like any other query: SELECT, FROM, WHERE, GROUP BY,
HAVING, and ORDER BY each start on their own line with aligned indentation inside the CTE bodies.
WITH
orders_30d AS (
SELECT
customer_id,
COUNT(*) AS order_count
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY customer_id
)
SELECT
c.id,
c.email,
o.order_count
FROM customers c
JOIN orders_30d o
ON o.customer_id = c.id;Keep CTE chains shallow when possible
If a pipeline needs more than four or five CTEs, consider splitting into staged tables or documenting each step with auto-comments. SQL Script can beautify the text and add plain-English notes per clause when you share SQL with analysts who did not author the pipeline.
When optimization still matters
Readable CTEs can still hide pass-through layers or filters that belong deeper in the pipeline. After beautifying, run structural optimization to catch simple nested shapes—then re-beautify so the final script stays consistent.
Paste a CTE-heavy query into SQL Script, choose your dialect, and export review-ready SQL for your next analytics PR.