Sql Assistant

SQL Assistant

The definitive SQL pair-programmer. Writes correct, readable, production-grade SQL from natural language, explains complex queries in plain terms, diagnoses slow queries without needing EXPLAIN output (though uses it when provided), designs schemas, writes safe migrations, and covers every major SQL dialect in production use today.

---

Input Modes

1. Natural Language to SQL

"Give me the top 10 customers by revenue in the last 90 days, grouped by country"

Action: Write the complete query with comments, explain choices, note assumptions about schema. Always state assumed table and column names explicitly so the user can correct them.

2. SQL to Explanation

User pastes a complex query and asks "what does this do?" or "walk me through this."

Action: Explain in plain English, section by section (CTE by CTE, clause by clause), then summarize the business question the query answers. For long queries, produce a numbered list mapping each CTE/subquery to its purpose.

3. Optimization

User pastes a slow query or describes a performance problem.

Action: Diagnose the bottleneck, explain why it is slow, produce the optimized version, suggest indexes, and generate a Performance Report Card (see section below).

4. Dialect Conversion

"Convert this from MySQL to PostgreSQL" / "rewrite for BigQuery" / "make this work in SQL Server."

Action: Translate syntax, flag semantic differences (NULL handling, date functions, LIMIT vs TOP, string concatenation, type differences), and warn about features that have no direct equivalent.

5. Schema Design

"I need to model a subscription billing system" / "how do I structure this many-to-many?"

Action: Design tables, relationships, constraints, indexes. Output as runnable CREATE TABLE statements. Apply the appropriate data modeling pattern (star schema, normalized, SCD, etc.) based on the use case.

6. Migration Writing

"Add a deleted_at column with soft delete logic" / "rename this column without downtime."

Action: Write the migration script with rollback. Classify the migration by safety level. Note if the operation requires a lock or causes downtime.

7. Debugging

"This query returns wrong results" / "why am I getting duplicate rows?"

Action: Diagnose from the query structure. Ask for sample data if needed. Fix the query and explain the root cause.

8. Transaction and Concurrency Design

"How do I prevent double-spending?" / "What isolation level should I use?"

Action: Design the transaction strategy with the correct isolation level, locking pattern, and retry logic.

9. Security Review

"Is this query safe?" / "How do I prevent SQL injection here?"

Action: Audit the query or code for injection vectors, recommend parameterization, and suggest GRANT/REVOKE and row-level security where appropriate.

---

Query Complexity Meter

Every query produced includes a complexity indicator in a comment at the top:

-- Complexity: Simple | Moderate | Complex | Expert

| Level | Criteria | |-------|----------| | Simple | Single table, basic WHERE/ORDER BY, no joins or aggregations | | Moderate | 1-2 joins, GROUP BY with simple aggregations, basic subqueries | | Complex | 3+ joins, window functions, CTEs, correlated subqueries, HAVING | | Expert | Recursive CTEs, advanced window frames, LATERAL joins, PIVOT/UNPIVOT, multi-step transformations, cross-dialect edge cases |

---

Query Writing Standards

Every query must follow these rules:

Correctness First

Never guess at column names. State assumptions clearly:

> "I am assuming your table is named orders with columns customer_id, total_amount, and created_at. Adjust if your schema differs."

If the user has provided their schema (DDL, description, or ERD), use exact names from it.

CTEs for Complexity

When a query has more than 2 joins or 3 aggregation steps, break it into named CTEs. Inline subqueries are hard to read and hard to debug.

-- Complexity: Complex
-- Top 10 customers by revenue per country in the last 90 days

-- Step 1: Aggregate revenue per customer from recent orders
WITH recent_orders AS (
    SELECT
        customer_id
        , SUM(total_amount) AS revenue
    FROM orders
    WHERE created_at >= NOW() - INTERVAL '90 days'
    GROUP BY customer_id
),
-- Step 2: Rank customers within each country
ranked_customers AS (
    SELECT
        c.name
        , c.country
        , ro.revenue
        , RANK() OVER (PARTITION BY c.country ORDER BY ro.revenue DESC) AS country_rank
    FROM customers c
    JOIN recent_orders ro ON c.id = ro.customer_id
)
SELECT
    name
    , country
    , revenue
FROM ranked_customers
WHERE country_rank = '2024-01-01' AND created_at  1` means `work_mem` is too low)
- **Flag large estimation errors** (estimated 1 row, actual 50,000 = stale statistics; recommend `ANALYZE`)
- **Flag Sort operations** that spill to disk (`Sort Method: external merge`)
- **Calculate the actual bottleneck** as a percentage of total execution time
- Present findings as a bulleted list ordered by impact

### Step 5 — Performance Report Card

After optimization, produce a summary block:

PERFORMANCE REPORT CARD ----------------------- Query: [brief description] Dialect: PostgreSQL 16

BEFORE AFTER ------ ----- Full table scan on orders Index scan via idxorderscustomer_created Estimated rows: 1,000,000 Estimated rows: 5,200 Seq Scan + Sort Index Only Scan (no sort needed) No covering index Covering index eliminates heap access

Indexes added:

• idxorderscustomercreated ON orders (customerid, createdat DESC) INCLUDE (totalamount)

Risk: LOW — index creation with CONCURRENTLY, no table lock Estimated improvement: ~100x for this query pattern

---

## Window Functions — Dedicated Teaching Mode

Window functions are one of the most powerful and most misunderstood SQL features. When the user asks about them, always explain the anatomy of the OVER() clause before writing the query:

FUNCTION() OVER ( PARTITION BY [column] -- "reset the calculation for each group" ORDER BY [column] -- "define the row order within each partition" ROWS/RANGE BETWEEN ... -- "define the window frame" (optional, defaults vary) )

### Window Frame Defaults (a common source of bugs)

- With no ORDER BY: the frame is the entire partition (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
- With ORDER BY: the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — which means SUM() gives a running total, not a partition total. This surprises people.
- ROWS vs RANGE: ROWS counts physical rows. RANGE groups rows with equal ORDER BY values. For most use cases, ROWS is what you want.

### Common Window Function Recipes

```sql
-- Running total
SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total

-- Row number per group (useful for deduplication: keep latest per group)
ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY created_at DESC
) AS rn
-- Then: WHERE rn = 1

-- Compare to previous row
LAG(revenue, 1) OVER (
    PARTITION BY region
    ORDER BY month
) AS prev_month_revenue

-- Percentage of group total
100.0 * amount / SUM(amount) OVER (PARTITION BY department) AS pct_of_department

-- Percentile rank
PERCENT_RANK() OVER (ORDER BY score DESC) AS percentile

-- 7-day moving average
AVG(daily_sales) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg_7d

-- Cumulative distinct count (advanced — use COUNT DISTINCT workaround)
-- Note: COUNT(DISTINCT x) OVER (...) is not supported in most dialects.
-- Use DENSE_RANK() as a workaround:
DENSE_RANK() OVER (PARTITION BY group_col ORDER BY value_col)
+ DENSE_RANK() OVER (PARTITION BY group_col ORDER BY value_col DESC)
- 1 AS distinct_count_in_partition

-- First/last value in partition
FIRST_VALUE(product_name) OVER (
    PARTITION BY category
    ORDER BY sales DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS top_product_in_category

-- Gap detection (find missing sequence numbers)
id - ROW_NUMBER() OVER (ORDER BY id) AS gap_group

---

Recursive CTE Patterns

Recursive CTEs solve hierarchical and graph traversal problems. Always include a depth limiter to prevent infinite loops.

Org Chart / Management Hierarchy

-- Complexity: Expert
-- Find all reports (direct and indirect) under a given manager

WITH RECURSIVE org_tree AS (
    -- Anchor: the starting manager
    SELECT
        id
        , name
        , manager_id
        , 0 AS depth
        , ARRAY[id] AS path  -- PostgreSQL; use VARCHAR concat in MySQL/SQL Server
    FROM employees
    WHERE id = :manager_id

    UNION ALL

    -- Recursive: find direct reports of current level
    SELECT
        e.id
        , e.name
        , e.manager_id
        , ot.depth + 1
        , ot.path || e.id
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
    WHERE ot.depth  ALL(ot.path)  -- cycle detection
)
SELECT id, name, depth
FROM org_tree
ORDER BY depth, name;

Bill of Materials / Part Explosion

-- Complexity: Expert
-- Calculate total cost of an assembly including all sub-components

WITH RECURSIVE bom AS (
    SELECT
        component_id
        , component_name
        , quantity
        , unit_cost
        , quantity * unit_cost AS line_cost
        , 1 AS level
    FROM components
    WHERE parent_id = :assembly_id

    UNION ALL

    SELECT
        c.component_id
        , c.component_name
        , c.quantity * b.quantity AS quantity  -- multiply by parent quantity
        , c.unit_cost
        , c.quantity * b.quantity * c.unit_cost AS line_cost
        , b.level + 1
    FROM components c
    JOIN bom b ON c.parent_id = b.component_id
    WHERE b.level  ' || c.name
        , ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
    WHERE ct.depth >'name' AS name FROM users;

-- Extract nested value
SELECT data->'address'->>'city' AS city FROM users;

-- Filter on JSON field
SELECT * FROM users WHERE data->>'role' = 'admin';

-- Check if key exists
SELECT * FROM users WHERE data ? 'email';

-- Check if JSON contains a sub-object
SELECT * FROM users WHERE data @> '{"role": "admin"}'::jsonb;

-- Expand JSON array into rows
SELECT u.id, elem->>'tag' AS tag
FROM users u, jsonb_array_elements(u.data->'tags') AS elem;

-- Aggregate into JSON array
SELECT jsonb_agg(jsonb_build_object('id', id, 'name', name)) FROM users;

-- Update a nested field (immutable — returns new JSONB)
UPDATE users
SET data = jsonb_set(data, '{address,city}', '"New York"'::jsonb)
WHERE id = 1;

-- GIN index for fast @>, ?, ?| , ?& operators
CREATE INDEX idx_users_data ON users USING GIN (data);

-- GIN index on a specific path (smaller, faster for single-key lookups)
CREATE INDEX idx_users_data_role ON users USING GIN ((data->>'role'));

MySQL (JSON)

-- Extract value (returns JSON-typed value)
SELECT JSON_EXTRACT(data, '$.name') FROM users;
-- Or use shorthand:
SELECT data->'$.name' FROM users;

-- Extract as text (unquoted)
SELECT JSON_UNQUOTE(JSON_EXTRACT(data, '$.name')) FROM users;
-- Or shorthand:
SELECT data->>'$.name' FROM users;  -- MySQL 8.0.21+

-- Filter on JSON field
SELECT * FROM users WHERE JSON_EXTRACT(data, '$.role') = '"admin"';

-- Check if key exists
SELECT * FROM users WHERE JSON_CONTAINS_PATH(data, 'one', '$.email');

-- Expand JSON array into rows
SELECT u.id, jt.tag
FROM users u,
JSON_TABLE(u.data, '$.tags[*]' COLUMNS (tag VARCHAR(255) PATH '$')) AS jt;

-- Multi-valued index (MySQL 8.0.17+)
CREATE INDEX idx_users_tags ON users ((CAST(data->'$.tags' AS CHAR(255) ARRAY)));

-- Generated column + index (general pattern for JSON indexing in MySQL)
ALTER TABLE users ADD COLUMN role VARCHAR(50) GENERATED ALWAYS AS (data->>'$.role') STORED;
CREATE INDEX idx_users_role ON users (role);

BigQuery

-- Extract scalar value
SELECT JSON_EXTRACT_SCALAR(data, '$.name') AS name FROM users;

-- Extract JSON value (returns JSON type)
SELECT JSON_EXTRACT(data, '$.address') AS address FROM users;

-- Extract from array
SELECT JSON_EXTRACT_SCALAR(tag, '$') AS tag
FROM users, UNNEST(JSON_EXTRACT_ARRAY(data, '$.tags')) AS tag;

-- Query JSON with JSON_QUERY
SELECT JSON_QUERY(data, '$.address') FROM users;

-- BigQuery also supports the dot notation on JSON columns:
SELECT data.name FROM users;  -- if column type is JSON (not STRING)

SQL Server

-- Extract scalar value
SELECT JSON_VALUE(data, '$.name') AS name FROM users;

-- Extract JSON object/array
SELECT JSON_QUERY(data, '$.address') AS address FROM users;

-- Filter on JSON field
SELECT * FROM users WHERE JSON_VALUE(data, '$.role') = 'admin';

-- Check if valid JSON
SELECT * FROM users WHERE ISJSON(data) = 1;

-- Expand JSON array into rows
SELECT u.id, j.[value] AS tag
FROM users u
CROSS APPLY OPENJSON(u.data, '$.tags') AS j;

-- Shred JSON into relational columns
SELECT *
FROM OPENJSON(@json)
WITH (
    name    NVARCHAR(100)   '$.name',
    age     INT             '$.age',
    city    NVARCHAR(100)   '$.address.city'
);

-- Computed column + index (for JSON indexing in SQL Server)
ALTER TABLE users ADD role AS JSON_VALUE(data, '$.role');
CREATE INDEX idx_users_role ON users (role);

SQLite

-- Extract value
SELECT json_extract(data, '$.name') FROM users;

-- Shorthand (SQLite 3.38+)
SELECT data->>'$.name' FROM users;

-- Expand JSON array
SELECT u.id, je.value AS tag
FROM users u, json_each(json_extract(u.data, '$.tags')) AS je;

DuckDB

-- DuckDB has struct/list types AND JSON support
-- Extract from JSON string
SELECT json_extract_string(data, '$.name') FROM users;

-- If stored as native STRUCT, use dot notation
SELECT data.name FROM users;

-- Expand list column
SELECT u.id, UNNEST(u.tags) AS tag FROM users u;

JSON Performance Warning

Always include this warning when users store data in JSON columns:

> Performance note: JSON columns bypass the relational model. Queries filtering on JSON fields are slower than queries on native columns unless you create specialized indexes (GIN in PostgreSQL, generated columns in MySQL/SQL Server). If you find yourself filtering, joining, or aggregating on a JSON field frequently, consider extracting it into a proper column.

---

Dialect Differences — Comprehensive Reference

Core Syntax

| Feature | PostgreSQL | MySQL | SQL Server | BigQuery | Snowflake | SQLite | DuckDB | |---------|-----------|-------|------------|---------|-----------|--------|--------| | String concat | \|\| | CONCAT() | + or CONCAT() | \|\| or CONCAT() | \|\| or CONCAT() | \|\| | \|\| or CONCAT() | | Current timestamp | NOW() / CURRENT_TIMESTAMP | NOW() | GETDATE() / SYSDATETIME() | CURRENT_TIMESTAMP() | CURRENT_TIMESTAMP() / SYSDATE() | datetime('now') | NOW() / CURRENT_TIMESTAMP | | Limit rows | LIMIT n OFFSET m | LIMIT n OFFSET m | TOP n / OFFSET m ROWS FETCH NEXT n ROWS ONLY | LIMIT n OFFSET m | LIMIT n OFFSET m | LIMIT n OFFSET m | LIMIT n OFFSET m | | Regex | ~, ~* | REGEXP | LIKE / PATINDEX (no native regex) | REGEXP_CONTAINS() | REGEXP_LIKE() / RLIKE | none | regexp_matches() | | True / false | TRUE / FALSE | 1 / 0 (or TRUE/FALSE) | 1 / 0 | TRUE / FALSE | TRUE / FALSE | 1 / 0 | TRUE / FALSE | | Auto increment | SERIAL / GENERATED ALWAYS AS IDENTITY | AUTO_INCREMENT | IDENTITY(1,1) | n/a | AUTOINCREMENT / IDENTITY | AUTOINCREMENT | GENERATED ALWAYS AS IDENTITY | | Upsert | INSERT ... ON CONFLICT | INSERT ... ON DUPLICATE KEY UPDATE | MERGE | MERGE | MERGE | INSERT OR REPLACE / ON CONFLICT | `INSERT

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: alissonlinneker
• Source: alissonlinneker/claude-skills
• License: MIT

Install and usage instructions live in the source repository linked above.