jeffallan/claude-skills

sql-pro

Optimizes SQL queries, designs database schemas, and troubleshoots performance issues.

View source

How to use

  1. Copy the installation command and review the source package before installing it in your own agent environment.
  2. Check the source document for required tools, credentials and supported hosts.
  3. Describe your input and desired deliverable; review the output against the source material.
npx skills add https://github.com/jeffallan/claude-skills --skill sql-pro

Before you start

  • Installation does not include a model subscription, API allowance or permission to access your files.
  • Repository stars and installation counts describe activity, not an independent quality or security test.
Original skill document

Rendered from the source repository. Headings, examples, code, tables, links, and referenced images are preserved.

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:

TopicReferenceLoad When
Query Patternsreferences/query-patterns.mdJOINs, CTEs, subqueries, recursive queries
Window Functionsreferences/window-functions.mdROW_NUMBER, RANK, LAG/LEAD, analytics
Optimizationreferences/optimization.mdEXPLAIN plans, indexes, statistics, tuning
Database Designreferences/database-design.mdNormalization, keys, constraints, schemas
Dialect Differencesreferences/dialect-differences.mdPostgreSQL vs MySQL vs SQL Server specifics

Quick-Reference Examples

CTE Pattern

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

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

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

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

Related by task

from this repository

More skills

All skills
jeffallan
Community

typescript-pro

Implements advanced TypeScript type systems, creates custom type guards, utility types, and branded types, and configures tRPC for end-to-end type safety. Use when building TypeScript applications requiring advanced generics, conditional or mapped types, discriminated unions, monorepo setup, or full-stack type safety with tRPC.

1h installs
2
GitHub stars
12.5K
Updated
Nov 20
jeffallan
Community

pandas-pro

Performs pandas DataFrame operations for data analysis, manipulation, and transformation. Use when working with pandas DataFrames, data cleaning, aggregation, merging, or time series analysis. Invoke for data manipulation tasks such as joining DataFrames on multiple keys, pivoting tables, resampling time series, handling NaN values with interpolation or forward-fill, groupby aggregations, type conversion, or performance optimization of large datasets.

1h installs
1
GitHub stars
12.5K
Updated
Nov 20
jeffallan
Community

rust-engineer

Writes, reviews, and debugs idiomatic Rust code with memory safety and zero-cost abstractions. Implements ownership patterns, manages lifetimes, designs trait hierarchies, builds async applications with tokio, and structures error handling with Result/Option. Use when building Rust applications, solving ownership or borrowing issues, designing trait-based APIs, implementing async/await concurrency, creating FFI bindings, or optimizing for performance and memory safety. Invoke for Rust, Cargo, ow

1h installs
1
GitHub stars
12.5K
Updated
Nov 20
jeffallan
Community

flutter-expert

Use when building cross-platform applications with Flutter 3+ and Dart. Invoke for widget development, Riverpod/Bloc state management, GoRouter navigation, platform-specific implementations, performance optimization.

1h installs
1
GitHub stars
12.5K
Updated
Nov 16