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

> Explore pm-data-analytics SQL generation capabilities. Transform natural language into optimized SQL queries with this technical guide and four-step workflow.

- Repository: [Pawel Huryn/pm-skills](https://github.com/phuryn/pm-skills)
- Tags: deep-dive
- Published: 2026-06-11

---

**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`](https://github.com/phuryn/pm-skills/blob/main/pm-data-analytics/skills/sql-queries/SKILL.md) and [`pm-data-analytics/commands/write-query.md`](https://github.com/phuryn/pm-skills/blob/main/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`](https://github.com/phuryn/pm-skills/blob/main/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`](https://github.com/phuryn/pm-skills/blob/main/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:

```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:

```sql
-- 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:

```sql
-- 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:

```sql
-- 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`](https://github.com/phuryn/pm-skills/blob/main/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`](https://github.com/phuryn/pm-skills/blob/main/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`](https://github.com/phuryn/pm-skills/blob/main/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`](https://github.com/phuryn/pm-skills/blob/main/pm-data-analytics/skills/sql-queries/SKILL.md)** (containing the core skill description and output format) and **[`pm-data-analytics/commands/write-query.md`](https://github.com/phuryn/pm-skills/blob/main/pm-data-analytics/commands/write-query.md)** (containing the user-facing command implementation and four-step workflow).