How to Generate SQL Queries from Natural Language in the pm‑data‑analytics Plugin

The pm‑data‑analytics plugin converts natural‑language questions into executable SQL using the /write-query command and the sql-queries skill, supporting multiple database dialects including BigQuery, PostgreSQL, and Snowflake.

The pm-data-analytics plugin in the phuryn/pm-skills repository provides a powerful interface to generate SQL queries from natural language. By combining a declarative skill architecture with a command-driven workflow, the plugin transforms plain‑English data requests into optimized, dialect‑specific SQL statements complete with performance annotations and schema awareness.

Architecture of the SQL Generation System

The plugin uses a two‑layer architecture that separates the skill definition from the command implementation, allowing the natural language processing logic to evolve independently of the user interface.

Skill Definition in SKILL.md

The core intelligence resides in pm-data-analytics/skills/sql-queries/SKILL.md. This markdown file contains YAML front‑matter that declares the skill's purpose, workflow stages, and capabilities. It defines how the system interprets metrics, dimensions, filters, and time ranges, and it houses the dialect‑specific logic for generating CTE‑rich queries with comments and edge‑case handling.

Command Implementation in write-query.md

The user‑facing interface is defined in pm-data-analytics/commands/write-query.md. This file specifies the command signature /write-query, argument hints, and the four‑step orchestration workflow that bridges user input to the skill backend. The command acts as a router, extracting intent from natural language before delegating SQL generation to the sql-queries skill.

How the /write-query Command Works

When you invoke /write-query, the plugin executes a four‑stage pipeline:

1. Parse Request – The command extracts the user's natural‑language intent, identifying metrics (e.g., "daily active users"), dimensions (e.g., "plan tier"), filters, and time ranges.

2. Resolve Schema – If you upload a schema file or diagram, the plugin reads table definitions, columns, and primary keys. If no schema is provided, the command prompts for the database type and falls back to a generic SaaS data model.

3. Generate SQL – The command invokes the sql-queries skill, which selects the appropriate dialect, maps the request onto tables and columns, and writes a readable query using Common Table Expressions (CTEs). The output includes dialect‑specific syntax, performance notes, and comments explaining edge cases.

4. Present and Iterate – The final output contains the SQL query, dialect used, tables referenced, a plain‑English explanation, assumptions made, and optional performance tips. You can request modifications, additional dialects, or companion queries in subsequent messages.

Supported Database Dialects

The sql-queries skill maintains dialect templates for major databases, including:

• BigQuery – Standard SQL with TIMESTAMP functions and COUNT(DISTINCT) optimizations
• PostgreSQL – Advanced CTE support and window functions
• MySQL – Version 8.0+ CTE syntax compatibility
• Snowflake – Semi‑structured data handling and clustering recommendations

Adding support for new dialects requires only updating the skill definition in SKILL.md; the /write-query command automatically picks up these changes because it delegates all generation logic to the skill.

Practical Usage Examples

Invoke the command using natural language in the pm‑toolkit console:

/write-query Show me daily active users for the last 30 days, broken down by plan tier

/write-query Find users who signed up last month but never completed onboarding

/write-query [upload a schema diagram] What's the conversion rate from trial to paid by cohort?

Sample Output Structure

The plugin returns a markdown‑formatted response containing the generated query and metadata:

## SQL Query: Daily Active Users by Plan Tier

**Dialect**: BigQuery  
**Tables used**: `users`, `sessions`

```sql
-- Daily active users per plan tier for the last 30 days
WITH daily_events AS (
  SELECT
    DATE(timestamp) AS day,
    u.plan_tier,
    COUNT(DISTINCT u.id) AS active_users
  FROM `project.dataset.sessions` s
  JOIN `project.dataset.users` u
    ON s.user_id = u.id
  WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  GROUP BY day, u.plan_tier
)
SELECT * FROM daily_events
ORDER BY day, plan_tier;

What this returns – One row per day‑plan‑tier combination, showing the count of distinct users active on that day.

Assumptions –

• sessions.timestamp stores event timestamps in UTC.
• users.plan_tier is a categorical column.

Notes –

• Consider clustering the sessions table on user_id for faster joins.
• If the result set is large, export to a table and use a materialized view for repeated runs.

## Edge‑Case Handling

The `pm-data-analytics` plugin includes robust handling for common data exploration scenarios:

- **No schema provided** – The command prompts for the database type and uses a default "common SaaS" schema to infer table relationships.
- **Ambiguous metrics** – Terms like "active users" trigger clarification requests before SQL generation to ensure accurate business logic.
- **Large dataset warnings** – Generated queries include comments suggesting indexes, partitioning strategies, or CTE restructuring to optimize performance.
- **Multiple dialects** – The command can output the same logical query in several dialects simultaneously upon request, useful for teams migrating between database systems.

## Summary

- The **`/write-query`** command in [`pm-data-analytics/commands/write-query.md`](https://github.com/phuryn/pm-skills/blob/main/pm-data-analytics/commands/write-query.md) provides the user interface for natural language SQL generation.
- The **`sql-queries`** skill in [`pm-data-analytics/skills/sql-queries/SKILL.md`](https://github.com/phuryn/pm-skills/blob/main/pm-data-analytics/skills/sql-queries/SKILL.md) contains the logic for dialect selection, schema mapping, and query optimization.
- The system supports **BigQuery, PostgreSQL, MySQL, Snowflake**, and other dialects through modular templates.
- The four‑step workflow (**Parse, Resolve, Generate, Present**) ensures schema awareness and iterative refinement.
- Edge cases like missing schemas or ambiguous metrics are handled through interactive clarification and performance annotations.

## Frequently Asked Questions

### What file contains the SQL generation logic for the pm‑data‑analytics plugin?

The SQL generation logic resides in [`pm-data-analytics/skills/sql-queries/SKILL.md`](https://github.com/phuryn/pm-skills/blob/main/pm-data-analytics/skills/sql-queries/SKILL.md) within the **phuryn/pm-skills** repository. This skill file defines the workflow for mapping natural language to database schemas and generating dialect‑specific queries.

### How does the plugin handle database schemas it hasn't seen before?

If you upload a schema file or diagram, the `/write-query` command reads the table definitions and relationships directly. If no schema is provided, the command asks you to specify the database type (e.g., PostgreSQL, BigQuery) and falls back to a generic SaaS data model to infer common tables like `users` and `events`.

### Can I generate the same query for multiple database dialects?

Yes. The `sql-queries` skill maintains separate dialect templates, allowing the command to output the same logical query in BigQuery SQL, PostgreSQL syntax, or MySQL format. You can request alternative dialects in the conversation thread after the initial generation.

### What happens if my natural language request is ambiguous?

When the skill encounters ambiguous terms like "active users" or "revenue," it asks clarifying questions before generating SQL. This prevents the creation of queries based on incorrect assumptions about column names or business logic definitions.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}