Text to SQL: window functions from plain English

Rankings, running averages, period-over-period growth, gap detection — ask in plain English and Chion generates the window function. All validated against your schema.

How Chion handles window functions

Rankings, rolling averages, and growth calculations.

Chion's pipeline maps intent ("month-over-month growth," "top per group," "running average") to the appropriate window function and PARTITION BY clause based on column type classification.

The two-layer validator checks that PARTITION BY references valid categorical columns and ORDER BY uses the correct sort direction. Frame specifications (ROWS BETWEEN) are generated automatically for rolling calculations.

Examples: English → window function queries

LAG, RANK, NTILE, and rolling aggregates.

"Month-over-month growth in signups"LAG window function
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;
"Running 7-day average of daily active users"ROWS BETWEEN
SELECT day, dau,
  AVG(dau) OVER (
    ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7d_avg
FROM daily_active_users;
"Rank employees by salary within each department"RANK + PARTITION BY
SELECT department, employee_name, salary,
  RANK() OVER (
    PARTITION BY department ORDER BY salary DESC
  ) AS salary_rank
FROM employees;
"Revenue contribution by top 20% of customers"NTILE
WITH ranked AS (
  SELECT customer_id,
    SUM(amount) AS total,
    NTILE(5) OVER (ORDER BY SUM(amount) DESC) AS quintile
  FROM payments GROUP BY customer_id
)
SELECT
  CASE WHEN quintile = 1 THEN 'Top 20%' ELSE 'Bottom 80%' END AS segment,
  SUM(total) AS revenue
FROM ranked GROUP BY segment;

Window Functions FAQ

Try window functions in plain English

Connect your PostgreSQL database. Ask about rankings, growth rates, or rolling averages.