majiayu000 SQL Pro
Purpose
Provides expert SQL development capabilities across major database platforms (PostgreSQL, MySQL, SQL Server, Oracle), specializing in complex query design, performance optimization, and database architecture. Masters ANSI SQL standards, platform-specific optimizations, and modern data patterns with focus on efficiency and scalability.
When to Use
- Writing complex SQL queries with joins, CTEs, window functions, or recursive queries
- Designing database schema for new application or refactoring existing schema
- Optimizing slow SQL queries with execution plan analysis
- Data migration between different database platforms (MySQL → PostgreSQL)
- Implementing stored procedures, functions, or triggers
- Building analytical reports with advanced aggregations and window functions
- Translating business requirements into SQL query logic
- Cross-platform SQL compatibility issues (different dialects)
Quick Start
Invoke this skill when:
- Writing complex queries with CTEs, window functions, or recursive patterns
- Designing or refactoring database schemas
- Optimizing slow queries with execution plan analysis
- Migrating data between different database platforms
- Implementing stored procedures, functions, or triggers
- Building analytical reports with advanced aggregations
Do NOT invoke when:
- PostgreSQL-specific features needed → Use postgres-pro
- MySQL-specific administration → Use database-administrator
- Simple CRUD operations → Use backend-developer
- ORM query patterns → Use appropriate language skill
Decision Framework
CTE vs Subquery vs JOIN Decision Tree
Query Requirement Analysis
│
├─ Need to reference result multiple times?
│ └─ YES → Use CTE (avoids duplicate subquery evaluation)
│ WITH user_totals AS (SELECT ...)
│ SELECT * FROM user_totals WHERE ...
│ UNION ALL
│ SELECT * FROM user_totals WHERE ...
│
├─ Recursive data traversal (hierarchy, graph)?
│ └─ YES → Use Recursive CTE (ONLY option for recursion)
│ WITH RECURSIVE tree AS (
│ SELECT ... -- anchor
│ UNION ALL
│ SELECT ... FROM tree ... -- recursive
│ )
│
├─ Simple lookup or filter?
│ └─ Use JOIN (most optimizable by query planner)
│ SELECT u.*, o.total
│ FROM users u
│ JOIN orders o ON u.id = o.user_id
│
├─ Correlated subquery in WHERE clause?
│ ├─ Checking existence → Use EXISTS (stops at first match)
│ │ WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = u.id)
│ │
│ └─ Value comparison → Use JOIN instead
│ -- BAD: WHERE (SELECT COUNT(*) FROM orders WHERE user_id = users.id) > 5
│ -- GOOD: JOIN (SELECT user_id, COUNT(*) as cnt FROM orders GROUP BY user_id)
│
└─ Readability vs Performance trade-off?
├─ Complex logic, readability critical → CTE
│ (Easier to understand, debug, maintain)
│
└─ Performance critical, simple logic → Subquery or JOIN
(Query planner can inline and optimize)
Window Function vs GROUP BY Decision Matrix
| Requirement | Solution | Example |
|---|---|---|
| Need aggregation + row-level detail | Window function | SELECT name, salary, AVG(salary) OVER () as avg_salary FROM employees |
| Only aggregated results needed | GROUP BY | SELECT dept, AVG(salary) FROM employees GROUP BY dept |
| Ranking/row numbering | Window function (ROW_NUMBER, RANK, DENSE_RANK) | ROW_NUMBER() OVER (ORDER BY sales DESC) |
| Running totals / moving averages | Window function with frame | SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) |
| LAG/LEAD (access previous/next rows) | Window function | LAG(price, 1) OVER (ORDER BY date) as prev_price |
| Percentile / NTILE | Window function | NTILE(4) OVER (ORDER BY score) as quartile |
| Simple count/sum/avg by group | GROUP BY (more efficient) | SELECT category, COUNT(*) FROM products GROUP BY category |
Red Flags → Escalate to Oracle
| Observation | Why Escalate | Example |
|---|---|---|
| Cartesian product in execution plan | Unintended cross join causing exponential rows | "Query returning millions of rows" |
| Complex multi-level recursive CTE performance | Advanced optimization needed | "Recursive CTE traversing 10+ levels with 100K nodes" |
| Cross-platform migration with incompatible features | Platform-specific feature mapping | "Migrating Oracle CONNECT BY to PostgreSQL recursive CTE" |
| Query with 10+ joins and complex logic | Architecture smell, potential redesign | "Single query joining 15 tables" |
| Temporal query with complex time-series logic | Advanced analytical pattern | "SCD Type 2 with historical snapshots" |
Core Capabilities
Advanced Query Patterns
- Common Table Expressions (CTEs) and recursive queries
- Window functions: ROW_NUMBER, RANK, LEAD, LAG, aggregate windows
- PIVOT/UNPIVOT operations for data transformation
- Hierarchical queries for tree/graph structures
- Temporal queries for time-based analysis
Query Optimization
- Execution plan analysis and interpretation
- Index selection strategies and covering indexes
- Statistics management and maintenance
- Query hints and plan guides (when necessary)
- Parallel query execution tuning
Index Design Patterns
- Clustered vs. non-clustered indexes
- Covering indexes for query optimization
- Filtered/partial indexes for selective queries
- Function-based/indexes on expressions
- Composite index column ordering
Quality Checklist
Query Performance:
- Execution time meets requirements (OLTP: <100ms, Analytics: <5s)
- EXPLAIN ANALYZE reviewed for all complex queries
- No sequential scans on large tables (unless intended)
- Indexes utilized effectively (check execution plan)
- No N+1 query patterns (correlated subqueries eliminated)
SQL Quality:
- Only necessary columns in SELECT (no SELECT *)
- Explicit table aliases used in multi-table queries
- Proper NULL handling (COALESCE, IS NULL vs = NULL)
- Data types match in comparisons (no implicit conversions)
- Parameterized queries used (SQL injection prevention)
Optimization:
- Window functions used instead of self-joins where applicable
- EXISTS used instead of NOT IN for better NULL handling
- Covering indexes suggested for frequent queries
- Query rewritten to eliminate correlated subqueries
Documentation:
- Complex query logic explained in comments
- CTE names descriptive and self-documenting
- Expected output format documented
- Performance characteristics documented
Additional Resources
- Detailed Technical Reference: See REFERENCE.md
- Code Examples & Patterns: See EXAMPLES.md