Index of every page on this site: llms.txt
All skills
jeffallan avatar

/sql-pro

@67c1961
by jeffallanjeffallan/claude-skills12k stars
1,202
Run it yourself

Optimizes SQL queries, designs database schemas, and troubleshoots performance issues. Use when a user asks why their query is slow, needs help writing complex joins or aggregations, mentions database performance issues, or wants to design or migrate a schema. Invoke for complex queries, window functions, CTEs, indexing strategies, query plan analysis, covering index creation, recursive queries, EXPLAIN/ANALYZE interpretation, before/after query benchmarking, or migrating queries between database dialects (PostgreSQL, MySQL, SQL Server, Oracle).

Use this Skill: https://skilld.dev/gh/jeffallan/claude-skills/sql-pro

Nothing lands on disk. Nothing to clean up.

Fork this Skill

Edit a local copy. It keeps the author and licence.

SKILL.md

≈140 tokens for metadata: the name and description. ≈1.1k when used: this file. ≈13k more on demand in 5 files.

Description uses 7.0% of example budget

Before choosing a Skill, your Agent reads its name and description. All available Skills share that space.

  • A shorter description leaves more room for other Skills. This entry exceeds our 1% size suggestion.

In our Claude Code example, all Skill names and descriptions share 8,000 characters. This Skill uses ≈561 characters, or 7.0%.

The 1% threshold is a size suggestion. Longer descriptions can still fit.

Your model, settings, and other Skills decide how much text your Agent can read.

Example settings and source

The example uses a 200k-token context and default Claude Code settings. The count includes the name, description, separators, and when_to_use when present. Codex also counts local file paths.

Skit's source and limits: Codex 0.160.1, Claude Code 2.1.292.

SQL Pro

Core Workflow

  1. Schema Analysis - Review database structure, indexes, query patterns, performance bottlenecks
  2. Design - Create set-based operations using CTEs, window functions, appropriate joins
  3. Optimize - Analyze execution plans, implement covering indexes, eliminate table scans
  4. Verify - Run EXPLAIN ANALYZE and confirm no sequential scans on large tables; if query does not meet sub-100ms target, iterate on index selection or query rewrite before proceeding
  5. Document - Provide query explanations, index rationale, performance metrics

Reference Guide

Load detailed guidance based on context:

Topic Reference Load When
Query Patterns references/query-patterns.md JOINs, CTEs, subqueries, recursive queries
Window Functions references/window-functions.md ROW_NUMBER, RANK, LAG/LEAD, analytics
Optimization references/optimization.md EXPLAIN plans, indexes, statistics, tuning
Database Design references/database-design.md Normalization, keys, constraints, schemas
Dialect Differences references/dialect-differences.md PostgreSQL vs MySQL vs SQL Server specifics

Quick-Reference Examples

CTE Pattern

-- Isolate expensive subquery logic for reuse and readability
WITH ranked_orders AS (
    SELECT
        customer_id,
        order_id,
        total_amount,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM orders
    WHERE status = 'completed'          -- filter early, before the join
)
SELECT customer_id, order_id, total_amount
FROM ranked_orders
WHERE rn = 1;                           -- latest completed order per customer

Window Function Pattern

-- Running total and rank within partition — no self-join required
SELECT
    department_id,
    employee_id,
    salary,
    SUM(salary)  OVER (PARTITION BY department_id ORDER BY hire_date) AS running_payroll,
    RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;

EXPLAIN ANALYZE Interpretation

-- PostgreSQL: always use ANALYZE to see actual row counts vs. estimates
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT *
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > NOW() - INTERVAL '30 days';

Key things to check in the output:

  • Seq Scan on large table → add or fix an index
  • actual rows ≫ estimated rows → run ANALYZE <table> to refresh statistics
  • Buffers: shared hit vs read → high read count signals missing cache / index

Before / After Optimization Example

-- BEFORE: correlated subquery, one execution per row (slow)
SELECT order_id,
       (SELECT SUM(quantity) FROM order_items oi WHERE oi.order_id = o.id) AS item_count
FROM orders o;

-- AFTER: single aggregation join (fast)
SELECT o.order_id, COALESCE(agg.item_count, 0) AS item_count
FROM orders o
LEFT JOIN (
    SELECT order_id, SUM(quantity) AS item_count
    FROM order_items
    GROUP BY order_id
) agg ON agg.order_id = o.id;

-- Supporting covering index (includes all columns touched by the query)
CREATE INDEX idx_order_items_order_qty
    ON order_items (order_id)
    INCLUDE (quantity);

Constraints

MUST DO

  • Analyze execution plans before recommending optimizations
  • Use set-based operations over row-by-row processing
  • Apply filtering early in query execution (before joins where possible)
  • Use EXISTS over COUNT for existence checks
  • Handle NULLs explicitly in comparisons and aggregations
  • Create covering indexes for frequent queries
  • Test with production-scale data volumes

MUST NOT DO

  • Use SELECT * in production queries
  • Use cursors when set-based operations work
  • Ignore platform-specific optimizations when targeting a specific dialect
  • Implement solutions without considering data volume and cardinality

Output Templates

When implementing SQL solutions, provide:

  1. Optimized query with inline comments
  2. Required indexes with rationale
  3. Execution plan analysis
  4. Performance metrics (before/after)
  5. Platform-specific notes if applicable

Maintained by @jeffallan, Principal Consultant at Synergetic Solutions

Documentation

Source: SKILL.md on GitHub

No rule matched.

skilld matched fixed text patterns in SKILL.md and file names. Patterns miss obfuscated code.

skilld run checks every file with the same patterns. It asks for approval before it loads a Skill with a behavior marked Needs approval.

1 alert1mo5 checks · Risk CRITICAL
  • Gen Agent Trust Hub1mo

    The skill contains a documentation link that has been flagged as malicious by automated security scanners. While the instructions for SQL optimization and schema design are standard, the presence of these flagged URLs in the skill's metadata and reference sections poses a significant security risk. Furthermore, the skill processes untrusted user SQL input without explicit boundary markers or sanitization, creating an attack surface for indirect prompt injection.

  • Socket1mo

    No alerts

  • Snyk1mo

    Risk: LOW · No issues

  • Runlayer8mo

    4/6 files flagged

  • ZeroLeaks7mo

    Score: 93/100 · 2 sections analyzed

Signed by skilld at 67c1961. This ties the file your Agent reads to that commit on GitHub. It does not review the instructions.

Last checked against GitHub 4 days ago.

Activeupdated 2 months ago
Other metadata
metadata
{
  "author": "https://github.com/Jeffallan",
  "company": "https://synergetic.solutions",
  "version": "1.1.0",
  "domain": "language",
  "triggers": "SQL optimization, query performance, database design, PostgreSQL, MySQL, SQL Server, window functions, CTEs, query tuning, EXPLAIN plan, database indexing",
  "role": "specialist",
  "scope": "implementation",
  "output-format": "code",
  "related-skills": "devops-engineer"
}
  • sql
  • postgresql
  • mysql
  • query-optimization
  • database-design
  • indexing
  • window-functions
  • ctes
  • performance-tuning
  • explain-analyze

README badge

README badge for jeffallan/claude-skills/sql-pro

Optimizes SQL queries and database schemas by analyzing execution plans, designing set-based operations with CTEs and window functions, and recommending covering indexes. Handles query performance troubleshooting, dialect-specific tuning for PostgreSQL/MySQL/SQL Server, and schema design with concrete EXPLAIN ANALYZE interpretation and before-after benchmarking.

Generated from the current SKILL.md.

Does this skill work with all SQL databases?
It covers PostgreSQL, MySQL, SQL Server, and Oracle. The skill includes dialect-specific guidance for differences between these platforms, though PostgreSQL is the primary focus for detailed examples.
What should I do if my query doesn't meet the sub-100ms performance target?
The skill's verify workflow requires iterating on index selection or query rewrite until the target is met. It emphasizes running EXPLAIN ANALYZE to identify sequential scans on large tables and creating covering indexes as needed.
Does this skill handle schema migration and database design?
Yes. It covers schema analysis, normalization, keys, constraints, and migrating queries between database dialects. Use it when designing new schemas or migrating existing ones across database platforms.
What optimization techniques does this skill focus on?
It emphasizes set-based operations (CTEs, window functions, joins), covering indexes, early filtering before joins, EXISTS over COUNT, and execution plan analysis via EXPLAIN ANALYZE. It avoids row-by-row processing like cursors.
Can this skill help with complex queries like recursive queries or window functions?
Yes. It includes reference guides and examples for CTEs, window functions (ROW_NUMBER, RANK, LAG/LEAD), recursive queries, and other advanced patterns.

Generated from the current SKILL.md. These answers refresh after source changes.