sql-pro

# 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 ```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) ...