SQL Query Generator: 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 mapped to the right function.

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 that 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 from plain English.

"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;

"Gaps in invoice numbers"

LAG gap detection
SELECT invoice_number,
  LAG(invoice_number) OVER (ORDER BY invoice_number) AS prev,
  invoice_number - LAG(invoice_number) OVER (ORDER BY invoice_number) AS gap
FROM invoices
WHERE invoice_number - LAG(invoice_number) OVER (ORDER BY invoice_number) > 1;

Window Functions FAQ

Try window functions in plain English

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