SQL Query Generation

This skill enables an AI agent to translate natural language questions into correct, efficient SQL queries. The agent maps user intent to the appropriate query constructs — joins, aggregations, window functions, CTEs, and subqueries — while respecting the target database schema. It also analyzes query performance with EXPLAIN plans and recommends optimizations such as indexing, predicate pushdown, and query restructuring.

Workflow

1. Parse the natural language request. Extract the analytical intent: what metric is being asked for, which entities are involved, what filters apply, and how results should be ordered or grouped. Distinguish between requests for aggregated summaries versus row-level detail.

2. Map to the database schema. Identify the relevant tables and columns from the schema. Resolve ambiguous references (e.g., "sales" could mean the orders table or the revenue column). Determine the join path between tables using foreign key relationships, avoiding unnecessary joins that inflate result sets.

3. Select the appropriate query constructs. Choose between simple aggregation, window functions, CTEs, or subqueries based on complexity. Use CTEs for multi-step calculations to improve readability. Use window functions for running totals, rankings, and comparisons within partitions. Prefer explicit JOINs over implicit comma-separated joins.

4. Generate the SQL query. Write syntactically correct SQL with consistent formatting: uppercase keywords, lowercase identifiers, aliased tables, and indented clauses. Include comments for complex logic. Always specify column aliases for computed expressions.

5. Validate and optimize. Run EXPLAIN (or EXPLAIN ANALYZE) on the generated query to inspect the execution plan. Look for full table scans, hash joins on large tables, and sort operations on unindexed columns. Recommend indexes or query rewrites when the estimated cost is high.

6. Return results with explanation. Present the query alongside a plain-language explanation of what it does, the expected output format, and any assumptions made about the schema or data.

Supported Technologies