Natural Language → SQL + KPI Extractor
Turn plain-English questions into safe, validated SQL and a clean KPI table you can run immediately.
Prompt Overview
Tips For You
Paste only the relevant schema and a few sample rows to keep the LLM focused. | State the desired grain upfront (e.g., daily by product). | Include time zone and currency assumptions to avoid mismatches. | Ask for test cases that you can quickly run in your SQL editor. | Keep SELECTs idempotent and comment major join assumptions.
From Operations TeamNexusAi TechnologyProblem It Solves
Bridges the gap between business questions and working SQL, reducing back-and-forth and query errors.
Read-only SQL generation
Strictly SELECT-focused queries with clear boundaries.
KPI-ready output
Returns a clean table schema aligned to dashboards.
Validation checklist
Built-in tests to catch common data pitfalls.
Dialect notes
Guidance for BigQuery, Snowflake, and Postgres.
AI Prompt Instructions
Act as: Senior Analytics Engineer and BI translator.
Why this task matters: Stakeholders ask business questions in natural language. Converting them into accurate SQL and a clean KPI output accelerates decisions and reduces rework.
Important boundaries:
- Generate only read-only SQL (SELECT). Never write, alter, or delete.
- Assume ANSI SQL; if dialect-specific (e.g., BigQuery, Snowflake), ask the user to specify.
- Prefer CTEs, clear aliases, and explicit joins; no SELECT *.
- Require filters, time windows, and grain assumptions to be explicit.
User inputs (paste below this prompt):
- Business question in plain English
- Target metric(s) and definitions if known
- Table schemas (names, columns, data types), row-level grain, and sample values
- Time zone, currency, and data freshness assumptions
- SQL dialect (if not ANSI)
Objectives:
1) Translate the question into a precise analytical intent and metric formula.
2) Produce safe, readable SQL with CTEs and inline comments.
3) Return a small KPI result shape ready for dashboards.
4) List validation checks and edge cases.
Analysis workflow:
1) Restate the question, identify entities, grain, windows, and filters.
2) Draft a metric definition and dimensions.
3) Propose a table-join plan with keys and null-handling strategy.
4) Generate SQL with: WITH ctes AS (...), final SELECT; include comment headers.
5) Provide test cases (expected rows/values) and explain how to sample data.
6) Outline how to adapt to Snowflake/BigQuery/Postgres dialects if needed.
Required output format:
- Section A: Analytical intent (bullets)
- Section B: SQL (fenced as a code block)
- Section C: KPI result schema (table of columns, types, meaning)
- Section D: Validation checks (list)
- Section E: Edge cases & limitations
Quality controls:
- Verify joins use correct keys and handle duplicates.
- Ensure time window predicates are explicit and sargable.
- Confirm metrics are aggregations at the stated grain.
Verification checklist:
- Does the SQL return one row per intended grain?
- Are NULLs and timezone handled?
- Are filters consistent with the business question?
Final instruction: Produce the full output. If any required input is missing, list concise clarifying questions first, then provide a draft assuming the most conservative defaults.
Expected Outcome
Section A: Intent - Metric: 7-day active users by product and plan - Grain: daily per product_id, plan Section B: SQL WITH events AS ( SELECT user_id, product_id, plan, event_time::date AS event_date FROM analytics.events WHERE event_time >= CURRENT_DATE - INTERVAL '30 days' ), active AS ( SELECT event_date, product_id, plan, COUNT(DISTINCT user_id) AS dau FROM events GROUP BY 1,2,3 ) SELECT event_date, product_id, plan, SUM(dau) OVER (PARTITION BY product_id, plan ORDER BY event_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS mau_7 FROM active; Section C: KPI Schema - event_date (date): Day - product_id (text): Product key - plan (text): Pricing plan - mau_7 (int): 7-day active users Section D: Validation - Check duplicate user_ids per day - Verify timezone alignment Section E: Edge Cases - Sparse events cause zeros; backfill days
Implementation Journey
Draft SQL from a question
Open ChatGPT and paste the business question, schema, and constraints using the prompt. Request ANSI SQL with CTEs and a KPI result shape. Expect a full query plus validation checks.
8 minRefine with code-aware suggestions
Open Codex in your editor to lint the SQL, adjust dialect specifics (e.g., BigQuery TIMESTAMP functions), and add safe predicates. Expect an optimized query ready to run.
6 minRun and interpret
Execute the SQL in your database, export a small sample to Google Sheets, and paste the result back into ChatGPT using an explainer prompt to confirm KPI shape and spot outliers.
10 min