A CTE is a named query block defined with WITH. It helps organize multi-step transformations so each stage has a clear purpose before final selection or aggregation.
- Name CTEs after business meaning, not temporary mechanics.
- Keep each CTE focused on one transformation step.
- Validate intermediate CTE outputs when results look off.
Use CTEs to map analytical stages
Northstar's retention report usually needs staged logic: paid orders, first order date by customer, and monthly repeat behavior. CTEs let each stage stay explicit and testable.
Compared with deeply nested subqueries, CTEs reduce mental overhead during code reviews and onboarding.
Balance readability and performance
PostgreSQL may inline or materialize CTEs depending on query shape and version behavior. The main reason to use CTEs is clarity first, then profile performance for heavy workloads.
If a CTE is reused many times, consider whether a temp table or persisted model is better for repeated reporting runs.
- Place filters early to reduce row volume downstream.
- Use EXPLAIN to compare alternatives for expensive reports.
- Avoid one giant CTE chain that hides final intent.
Build a monthly repeat-buyer metric
Northstar tracks repeat buyers as customers with at least two paid orders in the same month.
- Create paid_orders CTE filtered to status = 'paid'.
- Create customer_month_counts CTE grouped by customer_id and month with COUNT(*).
- Select monthly totals where count >= 2 and aggregate distinct customers per month.
Common mistakes
- Using vague CTE names like t1 and t2 that hide intent.
- Packing unrelated logic into a single CTE.
- Assuming CTE use always improves execution speed.
- Skipping tests of intermediate row counts between stages.
Try one
What is the main benefit of a CTE in a complex business query?
It organizes logic into named stages, which makes the query easier to read, validate, and maintain.
Sources
- PostgreSQL 18 Docs: WITH QueriesOfficial CTE syntax, behavior, and recursive query support.