SQL Query Generator: 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 displayed under the chart.

For hierarchy traversals, Chion generates WITH RECURSIVE with depth limits enforced by the validator. The output is a readable, auditable query 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.