How to use these examples
Each example shows a plain-English question and the verified PostgreSQL query Chion generates. Every query is schema-validated, LIMIT-enforced, and read-only.
Basic aggregations
"Total revenue last month"
sql
SELECT SUM(amount) AS total_revenue
FROM orders
WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
AND order_date < DATE_TRUNC('month', CURRENT_DATE);"Average order value by segment"
sql
SELECT segment, AVG(order_total) AS avg_order_value
FROM orders o JOIN customers c ON c.id = o.customer_id
GROUP BY segment ORDER BY avg_order_value DESC;JOINs
"Products not ordered in 90 days"
sql
SELECT p.product_name
FROM products p
LEFT JOIN orders o ON o.product_id = p.id
AND o.order_date >= CURRENT_DATE - INTERVAL '90 days'
WHERE o.id IS NULL;"Revenue per employee by department"
sql
SELECT d.department_name,
SUM(r.revenue) / COUNT(DISTINCT e.id) AS rev_per_employee
FROM departments d
JOIN employees e ON e.department_id = d.id
JOIN revenue r ON r.department_id = d.id
GROUP BY d.department_name;Window functions
"Month-over-month growth in signups"
sql
SELECT signup_month, signups,
LAG(signups) OVER (ORDER BY signup_month) AS prev_month,
ROUND(
(signups - LAG(signups) OVER (ORDER BY signup_month))::numeric
/ NULLIF(LAG(signups) OVER (ORDER BY signup_month), 0) * 100, 1
) AS growth_pct
FROM (
SELECT DATE_TRUNC('month', created_at) AS signup_month, COUNT(*) AS signups
FROM users GROUP BY 1
) sub;"Rank employees by salary within each department"
sql
SELECT department, employee_name, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;CTEs
"Cohort retention by signup month"
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;"Churn rate month over month"
sql
WITH monthly AS (
SELECT DATE_TRUNC('month', canceled_at) AS month, COUNT(*) AS churned
FROM subscriptions WHERE canceled_at IS NOT NULL GROUP BY 1
),
active AS (
SELECT DATE_TRUNC('month', period_start) AS month, COUNT(DISTINCT user_id) AS active
FROM subscriptions GROUP BY 1
)
SELECT m.month, m.churned, a.active,
ROUND(m.churned::numeric / NULLIF(a.active, 0) * 100, 1) AS churn_rate_pct
FROM monthly m JOIN active a ON a.month = m.month
ORDER BY m.month;Date arithmetic
"Orders per hour of day"
sql
SELECT EXTRACT(HOUR FROM order_timestamp) AS hour_of_day,
COUNT(*) AS order_count
FROM orders GROUP BY 1 ORDER BY 1;"P50 and P95 order fulfillment time"
sql
SELECT
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY fulfilled_at - ordered_at) AS p50_days,
PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY fulfilled_at - ordered_at) AS p95_days
FROM orders WHERE fulfilled_at IS NOT NULL;Every query above was generated by Chion's 13-phase pipeline with schema profiling, SQL contract enforcement, and two-layer validation.