Can Chion generate CTEs from plain English?

Yes. When your question requires intermediate result sets — cohort analysis, ranked subsets, running totals — Chion wraps the logic in WITH clauses automatically.

Does Chion support WITH RECURSIVE?

Yes, for hierarchy traversals (org charts, category trees). The validator enforces recursion depth limits to prevent runaway queries.

Why does Chion prefer CTEs over subqueries?

CTEs make every step visible in the SQL displayed under the chart. Named WITH clauses are more readable and auditable than nested subqueries.

Are CTE results cached by PostgreSQL?

PostgreSQL 12+ can inline CTEs for performance. Chion generates standard WITH syntax and lets the PostgreSQL planner optimize execution.

Text to SQL: CTEs from plain English

Ask a multi-step question. Chion wraps the logic in readable WITH clauses — cohort retention, ranked subsets, recursive org charts — and validates every CTE against your schema.

How Chion generates CTEs

Multi-step logic wrapped in readable WITH clauses.

When your question requires intermediate result sets, Chion wraps each logical step in a named WITH clause. CTEs are preferred over subqueries for readability — every step is visible in the SQL under the chart.

For hierarchy traversals, Chion generates WITH RECURSIVE with depth limits enforced by the validator. The output is auditable SQL you can copy into any PostgreSQL client.

Examples: English → CTE queries

Cohort retention, ranked subsets, recursive hierarchies.

"Cohort retention by signup month"CTE + date_trunc

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;

"Top category per customer"CTE + ROW_NUMBER

WITH ranked AS (
  SELECT customer_id, category,
    COUNT(*) AS purchases,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id ORDER BY COUNT(*) DESC
    ) AS rn
  FROM orders
  GROUP BY customer_id, category
)
SELECT customer_id, category, purchases
FROM ranked WHERE rn = 1;

"Running total of revenue by day"CTE + window SUM

WITH daily AS (
  SELECT order_date, SUM(amount) AS daily_revenue
  FROM orders
  GROUP BY order_date
)
SELECT order_date, daily_revenue,
  SUM(daily_revenue) OVER (ORDER BY order_date) AS running_total
FROM daily;

"Org chart traversal"WITH RECURSIVE

WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM employees e
  JOIN org o ON o.id = e.manager_id
  WHERE o.depth < 10
)
SELECT * FROM org ORDER BY depth, name;

CTE FAQ

Try CTEs in plain English

Connect your PostgreSQL database and ask a multi-step question.