Use when someone needs help writing or rewriting SQL — authoring complex joins, CTEs, window functions, or recursive queries — or designing a schema from scratch, normalizing an existing one, or migrating queries between database dialects.
Use when:
Do not use when:
database-optimizer)connection-pool-tuner)EXPLAIN ANALYZEFor each SQL task, provide:
PostgreSQL >= 10, MySQL >= 8.0)| Claude | You |
|---|---|
| Writes set-based query using CTEs or window functions | Provide sample data or schema DDL |
| Recommends covering index strategy | Run CREATE INDEX CONCURRENTLY in your environment |
| Reads EXPLAIN output and identifies plan patterns | Provide actual EXPLAIN ANALYZE output |
| Flags dialect-specific syntax differences | Test against your actual database version |
| Documents the before/after performance comparison | Validate with production-scale data volumes |
| 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 |
| Database Design | references/database-design.md | Normalization, keys, constraints |
| Dialect Differences | references/dialect-differences.md | PostgreSQL vs MySQL vs SQL Server |
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'
)
SELECT customer_id, order_id, total_amount
FROM ranked_orders
WHERE rn = 1; -- latest completed order per customer
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;
-- 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;
MUST DO:
MUST NOT DO:
SELECT * in production queriesdatabase-optimizer — server-level tuning after the query is optimizedconnection-pool-tuner — pool sizing if slow queries are exhausting connectionsspring-boot-engineer — for JPA query methods and @Query annotations