sql-pro

Name: sql-pro
Author: Jeffallan

11kstars971forks

How to install sql-pro?

jeffallan/claude-skills/sql-pro

$npx -y skills add Jeffallan/claude-skills --skill sql-pro

Installs into the current project.

Summary

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

Files · 1

View on GitHub

SKILL.md

1	# SQL Pro
2
3	## Core Workflow
4
5	1. Schema Analysis - Review database structure, indexes, query patterns, performance bottlenecks
6	2. Design - Create set-based operations using CTEs, window functions, appropriate joins
7	3. Optimize - Analyze execution plans, implement covering indexes, eliminate table scans
8	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
9	5. Document - Provide query explanations, index rationale, performance metrics
10
11	## Reference Guide
12
13	Load detailed guidance based on context:
14
15	\| Topic \| Reference \| Load When \|
16	\|-------\|-----------\|-----------\|
17	\| Query Patterns \| `references/query-patterns.md` \| JOINs, CTEs, subqueries, recursive queries \|
18	\| Window Functions \| `references/window-functions.md` \| ROW_NUMBER, RANK, LAG/LEAD, analytics \|
19	\| Optimization \| `references/optimization.md` \| EXPLAIN plans, indexes, statistics, tuning \|
20	\| Database Design \| `references/database-design.md` \| Normalization, keys, constraints, schemas \|
21	\| Dialect Differences \| `references/dialect-differences.md` \| PostgreSQL vs MySQL vs SQL Server specifics \|
22
23	## Quick-Reference Examples
24
25	### CTE Pattern
26	```sql
27	-- Isolate expensive subquery logic for reuse and readability
28	WITH ranked_orders AS (
29	SELECT
30	customer_id,
31	order_id,
32	total_amount,
33	ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
34	FROM orders
35	WHERE status = 'completed' -- filter early, before the join
36	)
37	SELECT customer_id, order_id, total_amount
38	FROM ranked_orders
39	WHERE rn = 1; -- latest completed order per customer
40	```
41
42	### Window Function Pattern
43	```sql
44	-- Running total and rank within partition — no self-join required
45	SELECT
46	department_id,
47	employee_id,
48	salary,
49	SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS running_payroll,
50	RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
51	FROM employees;
52	```
53
54	### EXPLAIN ANALYZE Interpretation
55	```sql
56	-- PostgreSQL: always use ANALYZE to see actual row counts vs. estimates
57	EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
58	SELECT *
59	FROM orders o
60	JOIN customers c ON c.id = o.customer_id
61	WHERE o.created_at > NOW() - INTERVAL '30 days';
62	```
63	Key things to check in the output:
64	- Seq Scan on large table → add or fix an index
65	- actual rows ≫ estimated rows → run `ANALYZE <table>` to refresh statistics
66	- Buffers: shared hit vs read → high `read` count signals missing cache / index
67
68	### Before / After Optimization Example
69	```sql
70	-- BEFORE: correlated subquery, one execution per row (slow)
71	SELECT order_id,
72	(SELECT SUM(quantity) FROM order_items oi WHERE oi.order_id = o.id) AS item_count
73	FROM orders o;
74
75	-- AFTER: single aggregation join (fast)
76	SELECT o.order_id, COALESCE(agg.item_count, 0) AS item_count
77	FROM orders o
78	LEFT JOIN (
79	SELECT order_id, SUM(quantity) AS item_count
80	FROM order_items
81	GROUP BY order_id
82	) agg ON agg.order_id = o.id;
83
84	-- Supporting covering index (includes all columns touched by the query)
85	CREATE INDEX idx_order_items_order_qty
86	ON order_items (order_id)
87	INCLUDE (quantity);
88	```
89
90	## Constraints
91
92	### MUST DO
93	- Analyze execution plans before recommending optimizations
94	- Use set-based operations over row-by-row processing
95	- Apply filtering early in query execution (before joins where possible)
96	- Use EXISTS over COUNT for existence checks
97	- Handle NULLs explicitly in comparisons and aggregations
98	- Create covering indexes for frequent queries
99	- Test with production-scale data volumes
100
101	### MUST NOT DO
102	- Use SELECT * in production queries
103	- Use cursors when set-based operations work
104	- Ignore platform-specific optimizations when targeting a specific dialect
105	- Implement solutions without considering data volume and cardinality
106
107	## Output Templates
108
109	When implementing SQL solutions, provide:
110	1. Optimized query with inline comments
111	2. Required

People who liked this also viewed

6 picks

Type

sql-pro

11kstars971forks

View on GitHub

How to install sql-pro?

jeffallan/claude-skills/sql-pro

$npx -y skills add Jeffallan/claude-skills --skill sql-pro

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# SQL Pro
2
3	## Core Workflow
4
5	1. Schema Analysis - Review database structure, indexes, query patterns, performance bottlenecks
6	2. Design - Create set-based operations using CTEs, window functions, appropriate joins
7	3. Optimize - Analyze execution plans, implement covering indexes, eliminate table scans
8	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
9	5. Document - Provide query explanations, index rationale, performance metrics
10
11	## Reference Guide
12
13	Load detailed guidance based on context:
14
15	\| Topic \| Reference \| Load When \|
16	\|-------\|-----------\|-----------\|
17	\| Query Patterns \| `references/query-patterns.md` \| JOINs, CTEs, subqueries, recursive queries \|
18	\| Window Functions \| `references/window-functions.md` \| ROW_NUMBER, RANK, LAG/LEAD, analytics \|
19	\| Optimization \| `references/optimization.md` \| EXPLAIN plans, indexes, statistics, tuning \|
20	\| Database Design \| `references/database-design.md` \| Normalization, keys, constraints, schemas \|
21	\| Dialect Differences \| `references/dialect-differences.md` \| PostgreSQL vs MySQL vs SQL Server specifics \|
22
23	## Quick-Reference Examples
24
25	### CTE Pattern
26	```sql
27	-- Isolate expensive subquery logic for reuse and readability
28	WITH ranked_orders AS (
29	SELECT
30	customer_id,
31	order_id,
32	total_amount,
33	ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
34	FROM orders
35	WHERE status = 'completed' -- filter early, before the join
36	)
37	SELECT customer_id, order_id, total_amount
38	FROM ranked_orders
39	WHERE rn = 1; -- latest completed order per customer
40	```
41
42	### Window Function Pattern
43	```sql
44	-- Running total and rank within partition — no self-join required
45	SELECT
46	department_id,
47	employee_id,
48	salary,
49	SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS running_payroll,
50	RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
51	FROM employees;
52	```
53
54	### EXPLAIN ANALYZE Interpretation
55	```sql
56	-- PostgreSQL: always use ANALYZE to see actual row counts vs. estimates
57	EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
58	SELECT *
59	FROM orders o
60	JOIN customers c ON c.id = o.customer_id
61	WHERE o.created_at > NOW() - INTERVAL '30 days';
62	```
63	Key things to check in the output:
64	- Seq Scan on large table → add or fix an index
65	- actual rows ≫ estimated rows → run `ANALYZE <table>` to refresh statistics
66	- Buffers: shared hit vs read → high `read` count signals missing cache / index
67
68	### Before / After Optimization Example
69	```sql
70	-- BEFORE: correlated subquery, one execution per row (slow)
71	SELECT order_id,
72	(SELECT SUM(quantity) FROM order_items oi WHERE oi.order_id = o.id) AS item_count
73	FROM orders o;
74
75	-- AFTER: single aggregation join (fast)
76	SELECT o.order_id, COALESCE(agg.item_count, 0) AS item_count
77	FROM orders o
78	LEFT JOIN (
79	SELECT order_id, SUM(quantity) AS item_count
80	FROM order_items
81	GROUP BY order_id
82	) agg ON agg.order_id = o.id;
83
84	-- Supporting covering index (includes all columns touched by the query)
85	CREATE INDEX idx_order_items_order_qty
86	ON order_items (order_id)
87	INCLUDE (quantity);
88	```
89
90	## Constraints
91
92	### MUST DO
93	- Analyze execution plans before recommending optimizations
94	- Use set-based operations over row-by-row processing
95	- Apply filtering early in query execution (before joins where possible)
96	- Use EXISTS over COUNT for existence checks
97	- Handle NULLs explicitly in comparisons and aggregations
98	- Create covering indexes for frequent queries
99	- Test with production-scale data volumes
100
101	### MUST NOT DO
102	- Use SELECT * in production queries
103	- Use cursors when set-based operations work
104	- Ignore platform-specific optimizations when targeting a specific dialect
105	- Implement solutions without considering data volume and cardinality
106
107	## Output Templates
108
109	When implementing SQL solutions, provide:
110	1. Optimized query with inline comments
111	2. Required

People who liked this also viewed

6 picks

Type