What is a CTE?
A Common Table Expression (CTE) is a named temporary result set defined with a WITH clause. CTEs make complex queries readable by breaking them into named steps.
Why Chion prefers CTEs
Chion generates CTEs instead of nested subqueries because every step is visible in the SQL displayed under the chart. Named WITH clauses are more readable and auditable.
Examples
Cohort retention
sql
WITH cohorts AS (
SELECT user_id, DATE_TRUNC('month', created_at) AS cohort FROM users
)
SELECT c.cohort,
DATE_TRUNC('month', e.event_date) AS activity_month,
COUNT(DISTINCT c.user_id) AS active_users
FROM cohorts c JOIN events e ON e.user_id = c.user_id
GROUP BY 1, 2 ORDER BY 1, 2;Running total
sql
WITH daily AS (
SELECT order_date, SUM(amount) AS daily_revenue FROM orders GROUP BY 1
)
SELECT order_date, daily_revenue,
SUM(daily_revenue) OVER (ORDER BY order_date) AS running_total
FROM daily;Recursive CTE (org chart)
sql
WITH RECURSIVE reports AS (
SELECT id, name, manager_id, 1 AS depth
FROM employees WHERE manager_id = 42
UNION ALL
SELECT e.id, e.name, e.manager_id, r.depth + 1
FROM employees e JOIN reports r ON r.id = e.manager_id
WHERE r.depth < 5
)
SELECT * FROM reports;Chion's validator enforces recursion depth limits on WITH RECURSIVE to prevent runaway queries.