SQL Generation Capabilities in pm-data-analytics: A Complete Technical Guide

The pm-data-analytics plugin provides a dedicated SQL Query Generator that transforms natural-language questions into optimized, dialect-specific SQL statements through a four-step workflow defined in pm-data-analytics/skills/sql-queries/SKILL.md and pm-data-analytics/commands/write-query.md.

The pm-data-analytics module within the phuryn/pm-skills repository offers robust SQL generation capabilities designed for product managers, analysts, and engineers who need to translate business questions into executable database queries. This functionality centers on the sql-queries skill and the /write-query command, which together orchestrate schema understanding, dialect-aware code generation, and performance optimization across multiple database platforms.

Core Architecture and Workflow

The SQL generation system operates through a structured four-step pipeline defined in the source files. Each step handles a specific phase of query construction, from initial intent parsing to final output refinement.

Understanding the Request

In pm-data-analytics/commands/write-query.md (lines 20-27), the command first parses the natural-language prompt to identify key components including metrics, dimensions, time ranges, grouping requirements, and output expectations. This extraction phase ensures the generator understands whether the user needs aggregation, filtering, joins, or specific sorting before proceeding to schema analysis.

Schema Retrieval and Analysis

The second step (lines 28-38) handles schema ingestion. If the user provides a schema file—whether SQL DDL, diagram descriptions, or documentation—the system extracts table definitions, column types, primary keys, foreign keys, and indexing hints. When no schema is provided, the command prompts for the database dialect and infers a generic SaaS schema structure to guide the generation process.

Optimized SQL Generation

The sql-queries skill in pm-data-analytics/skills/sql-queries/SKILL.md (lines 59-63) executes the actual code generation. This component produces dialect-specific queries that include:

• Common Table Expressions (CTEs) for improved readability
• Inline comments explaining complex logic
• Performance considerations such as partition pruning hints for BigQuery or clustering recommendations for Snowflake
• Alternative query approaches when multiple solutions exist

Explanation and Iteration

The final workflow step (lines 49-70) returns not just the raw SQL, but a comprehensive package containing the query, an English explanation of the logic, assumptions made about the schema, and testing suggestions. The system then offers follow-up actions including adding filters, creating companion queries, or building dashboard components.

Multi-Dialect Support and Optimization Features

The SQL generator supports BigQuery, PostgreSQL, MySQL, Snowflake, and SQL Server, automatically adapting syntax to match the target platform's requirements. Dialect selection occurs either through explicit user input or inference from uploaded schema files.

Schema awareness extends beyond simple table listings. The system detects foreign key relationships and proposes appropriate join conditions, while optimization features include automatic index suggestions, partitioning hints for time-series data, and flags for potentially expensive operations like full table scans.

Practical Code Examples

The following examples demonstrate the full capability stack across different database platforms.

BigQuery Daily Active Users

For a prompt requesting daily active users broken down by plan tier, the generator produces partition-aware BigQuery SQL:

-- BigQuery dialect
WITH events AS (
  SELECT
    DATE(event_timestamp) AS event_date,
    user_id,
    plan_tier
  FROM `my_project.analytics.events`
  WHERE event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
)
SELECT
  event_date,
  plan_tier,
  COUNT(DISTINCT user_id) AS dau
FROM events
GROUP BY event_date, plan_tier
ORDER BY event_date;

Performance note: The date filter enables partition pruning in BigQuery, reducing query costs and execution time.

PostgreSQL Cohort Conversion Analysis

When handling schema-aware cohort analysis, the generator creates multi-CTE queries that handle complex temporal joins:

-- PostgreSQL dialect
WITH trial_users AS (
  SELECT
    user_id,
    DATE_TRUNC('month', trial_start) AS month
  FROM users
  WHERE trial_start >= CURRENT_DATE - INTERVAL '6 months'
),
paid_users AS (
  SELECT
    user_id,
    DATE_TRUNC('month', paid_date) AS month
  FROM subscriptions
  WHERE paid_date IS NOT NULL
)
SELECT
  t.month,
  COUNT(p.user_id)::decimal / COUNT(t.user_id) AS conversion_rate
FROM trial_users t
LEFT JOIN paid_users p
  ON t.user_id = p.user_id AND p.month = t.month
GROUP BY t.month
ORDER BY t.month;

This query assumes the uploaded schema contains users (with trial_start) and subscriptions (with paid_date) tables, automatically detecting the relationship between these entities.

MySQL Cross-Dialect Generation

The system adapts date functions and syntax for MySQL-specific requirements:

-- MySQL dialect
SELECT
  p.product_id,
  p.name,
  SUM(o.amount) AS revenue
FROM orders o
JOIN products p ON o.product_id = p.product_id
WHERE QUARTER(o.order_date) = QUARTER(CURRENT_DATE)
  AND YEAR(o.order_date) = YEAR(CURRENT_DATE)
GROUP BY p.product_id, p.name
ORDER BY revenue DESC
LIMIT 10;

Snowflake Performance Optimization

For Snowflake deployments, the generator includes clustering and indexing recommendations alongside the query:

-- Snowflake dialect
SELECT
  user_id,
  COUNT(*) AS session_count
FROM sessions
WHERE session_timestamp >= DATEADD(day, -7, CURRENT_DATE())
GROUP BY user_id
ORDER BY session_count DESC;

Performance note: Consider clustering on session_timestamp and user_id for faster filtering and grouping operations.

Summary

• The /write-query command in pm-data-analytics/commands/write-query.md provides the user-facing entry point for SQL generation.
• The sql-queries skill in pm-data-analytics/skills/sql-queries/SKILL.md defines the core capabilities, dialect support, and output formatting.
• The system supports BigQuery, PostgreSQL, MySQL, Snowflake, and SQL Server with dialect-specific syntax optimization.
• Schema awareness includes foreign key detection, relationship mapping, and automatic join proposal.
• Generated queries include CTEs, inline comments, and performance notes for maintainability and optimization.
• The workflow supports iterative refinement through follow-up actions and assumption documentation.

Frequently Asked Questions

What SQL dialects does pm-data-analytics support?

The SQL generation capability supports BigQuery, PostgreSQL, MySQL, Snowflake, and SQL Server, with syntax automatically adapted to each platform's specific requirements for date functions, window operations, and query structure.

How does the schema detection work in the SQL generator?

The system accepts uploaded schema files in SQL DDL, CSV, or diagram markdown formats, extracting table definitions, column types, primary keys, and foreign keys from pm-data-analytics/commands/write-query.md (lines 28-38). If no schema is provided, the command infers a generic SaaS schema based on the specified dialect.

Can I use the SQL generation for complex cohort analysis?

Yes, the sql-queries skill can be called directly from other commands to produce underlying queries for cohort analysis, as demonstrated in the PostgreSQL conversion rate example using multiple CTEs to handle trial and paid user cohorts.

Where are the SQL generation capabilities defined in the codebase?

The capabilities are defined in two primary locations: pm-data-analytics/skills/sql-queries/SKILL.md (containing the core skill description and output format) and pm-data-analytics/commands/write-query.md (containing the user-facing command implementation and four-step workflow).

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