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