SQL Pro
Core Workflow
- Schema Analysis - Review database structure, indexes, query patterns, performance bottlenecks
- Design - Create set-based operations using CTEs, window functions, appropriate joins
- Optimize - Analyze execution plans, implement covering indexes, eliminate table scans
- 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
- 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
Window Function Pattern
-- 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
-- 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
-- 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:
- Optimized query with inline comments
- Required indexes with rationale
- Execution plan analysis
- Performance metrics (before/after)
- Platform-specific notes if applicable
Maintained by @jeffallan, Principal Consultant at Synergetic Solutions
Documentation
1---2name: sql-pro3description: Optimizes SQL queries, designs database schemas, and troubleshoots performance issues. Use when a user asks why their query is slow, needs help writing complex joins or aggregations, mentions database performance issues, or wants to design or migrate a schema. Invoke for complex queries, window functions, CTEs, indexing strategies, query plan analysis, covering index creation, recursive queries, EXPLAIN/ANALYZE interpretation, before/after query benchmarking, or migrating queries between database dialects (PostgreSQL, MySQL, SQL Server, Oracle).4license: MIT5metadata:6 source: https://skillmd.com/skills/tuyv/sql-pro7 author: https://github.com/Jeffallan8 company: https://synergetic.solutions9 version: "1.1.0"10 domain: language11 triggers: SQL optimization, query performance, database design, PostgreSQL, MySQL, SQL Server, window functions, CTEs, query tuning, EXPLAIN plan, database indexing12 role: specialist13 scope: implementation14 output-format: code15 related-skills: devops-engineer16---1718# SQL Pro1920## Core Workflow21221. **Schema Analysis** - Review database structure, indexes, query patterns, performance bottlenecks232. **Design** - Create set-based operations using CTEs, window functions, appropriate joins243. **Optimize** - Analyze execution plans, implement covering indexes, eliminate table scans254. **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 proceeding265. **Document** - Provide query explanations, index rationale, performance metrics2728## Reference Guide2930Load detailed guidance based on context:3132| Topic | Reference | Load When |33|-------|-----------|-----------|34| Query Patterns | `references/query-patterns.md` | JOINs, CTEs, subqueries, recursive queries |35| Window Functions | `references/window-functions.md` | ROW_NUMBER, RANK, LAG/LEAD, analytics |36| Optimization | `references/optimization.md` | EXPLAIN plans, indexes, statistics, tuning |37| Database Design | `references/database-design.md` | Normalization, keys, constraints, schemas |38| Dialect Differences | `references/dialect-differences.md` | PostgreSQL vs MySQL vs SQL Server specifics |3940## Quick-Reference Examples4142### CTE Pattern43```sql44-- Isolate expensive subquery logic for reuse and readability45WITH ranked_orders AS (46 SELECT47 customer_id,48 order_id,49 total_amount,50 ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn51 FROM orders52 WHERE status = 'completed' -- filter early, before the join53)54SELECT customer_id, order_id, total_amount55FROM ranked_orders56WHERE rn = 1; -- latest completed order per customer57```5859### Window Function Pattern60```sql61-- Running total and rank within partition — no self-join required62SELECT63 department_id,64 employee_id,65 salary,66 SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS running_payroll,67 RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank68FROM employees;69```7071### EXPLAIN ANALYZE Interpretation72```sql73-- PostgreSQL: always use ANALYZE to see actual row counts vs. estimates74EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)75SELECT *76FROM orders o77JOIN customers c ON c.id = o.customer_id78WHERE o.created_at > NOW() - INTERVAL '30 days';79```80Key things to check in the output:81- **Seq Scan on large table** → add or fix an index82- **actual rows ≫ estimated rows** → run `ANALYZE <table>` to refresh statistics83- **Buffers: shared hit** vs **read** → high `read` count signals missing cache / index8485### Before / After Optimization Example86```sql87-- BEFORE: correlated subquery, one execution per row (slow)88SELECT order_id,89 (SELECT SUM(quantity) FROM order_items oi WHERE oi.order_id = o.id) AS item_count90FROM orders o;9192-- AFTER: single aggregation join (fast)93SELECT o.order_id, COALESCE(agg.item_count, 0) AS item_count94FROM orders o95LEFT JOIN (96 SELECT order_id, SUM(quantity) AS item_count97 FROM order_items98 GROUP BY order_id99) agg ON agg.order_id = o.id;100101-- Supporting covering index (includes all columns touched by the query)102CREATE INDEX idx_order_items_order_qty103 ON order_items (order_id)104 INCLUDE (quantity);105```106107## Constraints108109### MUST DO110- Analyze execution plans before recommending optimizations111- Use set-based operations over row-by-row processing112- Apply filtering early in query execution (before joins where possible)113- Use EXISTS over COUNT for existence checks114- Handle NULLs explicitly in comparisons and aggregations115- Create covering indexes for frequent queries116- Test with production-scale data volumes117118### MUST NOT DO119- Use SELECT * in production queries120- Use cursors when set-based operations work121- Ignore platform-specific optimizations when targeting a specific dialect122- Implement solutions without considering data volume and cardinality123124## Output Templates125126When implementing SQL solutions, provide:1271. Optimized query with inline comments1282. Required indexes with rationale1293. Execution plan analysis1304. Performance metrics (before/after)1315. Platform-specific notes if applicable132133Maintained by [@jeffallan](https://github.com/jeffallan), Principal Consultant at [Synergetic Solutions](https://synergetic.solutions)134135[Documentation](https://jeffallan.github.io/claude-skills/skills/language/sql-pro/)