SQL Pro

SQL Pro

When to Use / When Not to Use

Use when:

• Writing or rewriting SQL queries: joins, CTEs, window functions, recursive queries
• Designing or normalizing a schema
• Interpreting an EXPLAIN plan for a slow query
• Migrating SQL between PostgreSQL, MySQL, and SQL Server dialects

Do not use when:

• The bottleneck is server-level config (use database-optimizer)
• The issue is connection pool exhaustion (use connection-pool-tuner)

Process

1. Schema Analysis — Review table structure, existing indexes, query patterns
2. Design — Draft set-based operations using CTEs, window functions, appropriate joins
3. Version Check — Confirm target engine and version; flag any feature requiring a minimum version
4. Optimize — Analyze execution plans; implement covering indexes; eliminate table scans
5. Verify — Run EXPLAIN ANALYZE and confirm no sequential scans on large tables; iterate until sub-100ms target is met
6. Document — Provide query explanation, index rationale, performance metrics, and minimum version requirements

Output Template

For each SQL task, provide:

1. Optimized query with inline comments
2. Required indexes with rationale
3. Execution plan analysis (key patterns found)
4. Performance metrics (before/after)
5. Platform-specific notes if applicable
6. Minimum version requirements (e.g., PostgreSQL >= 10, MySQL >= 8.0)

What Claude Does / What You Do

Reference Guide

Quick-Reference Examples

CTE Pattern

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'
)
SELECT customer_id, order_id, total_amount
FROM ranked_orders
WHERE rn = 1;  -- latest completed order per customer

Window Function Pattern

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;

Before / After Optimization

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

Constraints

MUST DO:

• Analyze execution plans before recommending optimizations
• Use set-based operations over row-by-row processing
• Apply filtering early (before joins where possible)
• Use EXISTS over COUNT for existence checks
• Handle NULLs explicitly

MUST NOT DO:

• Use SELECT * in production queries
• Use cursors when set-based operations work
• Implement solutions without considering data volume and cardinality

Related Skills

• database-optimizer — server-level tuning after the query is optimized
• connection-pool-tuner — pool sizing if slow queries are exhausting connections
• spring-boot-engineer — for JPA query methods and @Query annotations