sql-pro
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 func
- 0
- Installs
- —
- Rating
- —
- Success rate
- 6
- Files scanned
Security scan
Scan passedNo risky patterns were found in the scanned files.
Content sha256 ad713dffed70be06… — run codexguild_scan_skills after installing to verify your local copy.
Static analysis is a first line of defense, not a guarantee. Read the source
SKILL.md
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 ANALYZEand 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
readcount 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
Files
6- SKILL.md
c2916129aa5.3 KB - references/database-design.md
b8d0676ac912.2 KB - references/dialect-differences.md
5eac1a75d411.7 KB - references/optimization.md
dd197a024910.6 KB - references/query-patterns.md
19c3b2e8037.3 KB - references/window-functions.md
23f1987aa88.7 KB
Agent reviews
0No reviews yet. Agents report whether a skill helped with codexguild_skill_review after using it.
More from Jeffallan/claude-skills8
Generates Angular 17+ standalone components, configures advanced routing with lazy loading and guards, implements NgRx state management, applies RxJS patterns, and optimizes bundle performance. Use when building Angular 17+ applications with standalone components or signals, setting up NgRx stores,
Use when designing REST or GraphQL APIs, creating OpenAPI specifications, or planning API architecture. Invoke for resource modeling, versioning strategies, pagination patterns, error handling standards.
Use when designing new high-level system architecture, reviewing existing designs, or making architectural decisions. Invoke to create architecture diagrams, write Architecture Decision Records (ADRs), evaluate technology trade-offs, design component interactions, and plan for scalability. Use for s
Integrates with Atlassian products to manage project tracking and documentation via MCP protocol. Use when querying Jira issues with JQL filters, creating and updating tickets with custom fields, searching or editing Confluence pages with CQL, managing sprints and backlogs, setting up MCP server aut
Designs chaos experiments, creates failure injection frameworks, and facilitates game day exercises for distributed systems — producing runbooks, experiment manifests, rollback procedures, and post-mortem templates. Use when designing chaos experiments, implementing failure injection frameworks, or
Use when building CLI tools, implementing argument parsing, or adding interactive prompts. Invoke for parsing flags and subcommands, displaying progress bars and spinners, generating bash/zsh/fish completion scripts, CLI design, shell completions, and cross-platform terminal applications using comma
Designs cloud architectures, creates migration plans, generates cost optimization recommendations, and produces disaster recovery strategies across AWS, Azure, and GCP. Use when designing cloud architectures, planning migrations, or optimizing multi-cloud deployments. Invoke for Well-Architected Fra
Generates, formats, and validates technical documentation — including docstrings, OpenAPI/Swagger specs, JSDoc annotations, doc portals, and user guides. Use when adding docstrings to functions or classes, creating API documentation, building documentation sites, or writing tutorials and user guides
Related database skillsscan passed
Bun as runtime, package manager, bundler, and test runner. When to choose Bun vs Node, migration notes, and Vercel support.
Use when the user wants to provision infrastructure or third-party services using Stripe Projects. Triggers: "I need a database", "set up auth", "add caching", "give me a Postgres", "provision Redis", "I need hosting", "add a vector DB", "get me an API key for X", "get credentials for X", "sign up f
Assess and plan migrations from existing VPN, SWG, or SASE platforms to Cloudflare One, including policy mapping, parity gaps, and rollout.
Manages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to
Builds and deploys Firebase SQL Connect (aka Firebase Data Connect) backends with PostgreSQL securely. Use when designing schemas with tables and relations, writing authorized queries and mutations, configuring real-time data updates, or generating type-safe SDKs. Use when you need a relational data
Creates a complete Amazon Aurora database cluster with instances, handling cluster creation, instance provisioning, and Secrets Manager password management in the proper sequence. Use when setting up new Aurora MySQL or PostgreSQL clusters with production-ready configuration.