How to Use CTEs in Redshift

What problem do CTEs solve in Redshift?

CTEs replace nested sub-queries with readable, reusable result sets. You define them once, then reference them like regular tables in the same statement.

How does the WITH keyword work?

WITH introduces one or more CTEs. Each CTE has a name, an optional column list, and a SELECT that builds its data. Subsequent SELECT, INSERT, UPDATE, or DELETE statements can reference the CTEs.

Can I chain multiple CTEs?

Yes. Separate CTE definitions with commas.Later CTEs can reference earlier ones, enabling step-by-step transformations.

Why prefer CTEs over temporary tables?

CTEs live only for the duration of the statement, require no clean-up, and avoid catalog bloat.They also make complex queries self-contained and easier to version-control.

What is the basic CTE workflow?

1) Write WITH cte_name AS (SELECT ...) 2) Add more CTEs if needed 3) Finish with a main query that refers to them.

Example: calculating customer lifetime value

The example below shows two CTEs: recent_orders and customer_ltv.The final SELECT joins them to the Customers table.

WITH recent_orders AS (
SELECT customer_id, SUM(total_amount) AS last_90_days
FROM Orders
WHERE order_date >= CURRENT_DATE - INTERVAL '90 day'
GROUP BY customer_id
),
customer_ltv AS (
SELECT o.customer_id, SUM(o.total_amount) AS lifetime_value
FROM Orders o
GROUP BY o.customer_id
)
SELECT c.id, c.name, clv.lifetime_value, ro.last_90_days
FROM Customers c
LEFT JOIN customer_ltv clv ON clv.customer_id = c.id
LEFT JOIN recent_orders ro ON ro.customer_id = c.id;

Best practices for Redshift CTEs?

Keep CTEs small; Redshift materializes each CTE, so avoid SELECT *.Index with DISTKEY/SORTKEY on underlying tables to speed joins. Use ANALYZE to keep stats fresh.

When should you avoid CTEs?

Use derived tables or temp tables when the same interim result is needed across multiple statements, or when the CTE would duplicate large data sets several times within a single query.

.