hoavdc/CodexKit / skills/codexkit-sql-query-builder
codexkit-sql-query-builder
UnverifiedBuild production-ready analytical SQL queries using CTEs, window functions, aggregations, and data transformations. Translates business questions into well-documented, performant queries. Use when writing reporting queries, building data pipelines, or answering ad-hoc business questions with SQL.
SQL Query Builder
When to Use
- When a business question needs to be answered with data
- When building reporting queries or data pipeline transformations
- When optimizing slow queries or refactoring complex SQL
- When translating stakeholder requirements into analytical SQL
Procedure
Step 1 — Clarify the Question
Translate the business question into a precise data question:
- Business: "How are our sales trending?"
- Data: "Monthly revenue by product category for the last 12 months, with month-over-month growth rate"
Document:
- Expected output columns and format
- Filters and date ranges
- Granularity (daily, weekly, monthly)
- Sort order
Step 2 — Identify Tables & Joins
Map the data model:
| Table | Role | Key Columns | Join |
|---|---|---|---|
| orders | Fact | order_id, customer_id, order_date, total | Primary |
| products | Dimension | product_id, category, name | orders.product_id = products.id |
| customers | Dimension | customer_id, region, segment | orders.customer_id = customers.id |
Join type selection:
- INNER when both sides must exist
- LEFT when the left side may have no match (preserve all orders even without customer data)
- Never use RIGHT JOIN — rewrite as LEFT JOIN for clarity
Step 3 — Build CTE Pipeline
Structure complex queries as a pipeline of CTEs:
WITH
-- Step 1: Filter and clean base data
base AS (
SELECT ...
FROM orders
WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '12 months')
),
-- Step 2: Aggregate to desired granularity
monthly AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
category,
SUM(total) AS revenue,
COUNT(DISTINCT customer_id) AS unique_customers
FROM base
JOIN products USING (product_id)
GROUP BY 1, 2
),
-- Step 3: Add calculations (window functions)
with_growth AS (
SELECT *,
LAG(revenue) OVER (PARTITION BY category ORDER BY month) AS prev_revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (PARTITION BY category ORDER BY month))
/ NULLIF(LAG(revenue) OVER (PARTITION BY category ORDER BY month), 0), 1) AS mom_growth_pct
FROM monthly
)
-- Final output
SELECT month, category, revenue, unique_customers, mom_growth_pct
FROM with_growth
ORDER BY category, month;
Step 4 — Common Patterns Reference
| Pattern | When to Use | SQL |
|---|---|---|
| Running total | Cumulative metrics | SUM(x) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) |
| Rank within group | Top N per category | ROW_NUMBER() OVER (PARTITION BY cat ORDER BY val DESC) |
| Period comparison | YoY, MoM | LAG(val, 12) OVER (ORDER BY month) |
| Deduplication | Remove exact dupes | ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated DESC) |
| Cohort analysis | Retention / engagement | First-action date as cohort, then activity by period |
| Pivot | Rows to columns | CASE WHEN + SUM or database-specific PIVOT |
Step 5 — Performance Notes
Add comments for production queries:
- Estimated row counts at each CTE stage
- Index usage hints
- Known limitations or edge cases
- Data freshness assumptions
Inputs
| Input | Required | Format |
|---|---|---|
| Business question | Yes | Plain language |
| Table schema | Yes | Table names, columns, types |
| Database dialect | Recommended | PostgreSQL / MySQL / BigQuery / Snowflake |
| Sample data | Recommended | To validate output |
| Performance requirements | Optional | Max execution time |
Output
## Query — [Business Question]
### Question
"What is the monthly revenue by category for the last 12 months with growth rates?"
### Query
[SQL with CTEs, comments, and formatting]
### Output Schema
| Column | Type | Description |
|--------|------|-------------|
| month | DATE | First day of month |
| category | VARCHAR | Product category |
| revenue | DECIMAL | Total revenue |
| unique_customers | INT | Distinct customers |
| mom_growth_pct | DECIMAL | Month-over-month growth % |
### Performance Notes
- Base table ~2M rows, filtered to ~200K
- Index on orders(order_date) recommended
- Runs in ~3s on PostgreSQL 15
Definition of Done
- Business question clearly translated to data question
- Tables and joins documented
- Query uses CTE pipeline (no nested subqueries)
- Window functions used where appropriate
- Output columns described
- Performance notes included
Quality Criteria
- Data sources and assumptions are explicitly stated
- Calculations are reproducible from provided inputs
- Visualizations or tables have clear labels, units, and time ranges
- Caveats and confidence levels are documented for estimates
Verification (4C)
| Check | Question |
|---|---|
| Correctness | Are formulas, aggregations, and statistical methods applied correctly? |
| Completeness | Does the analysis cover all requested metrics and time ranges? |
| Context-fit | Are the chosen metrics relevant to the business question being answered? |
| Consequence | If this data were used for a decision today, what blind spots remain? |
Edge Cases
- Missing or incomplete data — Document gaps and their potential impact on conclusions. Provide ranges instead of point estimates.
- Outliers skewing results — Report with and without outliers. Document the decision to include or exclude.
- Changing data definitions mid-period — Split analysis at the change boundary and note the schema difference.
Changelog
- v1.1.0 — Added Tier 2 verification checklist and examples for query safety.
- v1.0.0 — Initial release
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| writing-skillsobra/superpowersUse when creating new skills, editing existing skills, or verifying skills work before deployment | Stars: 318k | Updated | Claude CodeCodex | Unverified | Install |
| xlsxanthropics/skillsUse this skill any time a spreadsheet file is the primary input or output. This means any task where the user wants to: open, read, edit, or fix an existing .xlsx, .xlsm, .xltx, .csv, or .tsv file (e.g., adding columns, computing formulas, formatting, charting, cleaning messy data); create a new spreadsheet from scratch or from other data sources; or convert between tabular file formats. Trigger especially when the user references a spreadsheet file by name or path — even casually (like "the xlsx in my downloads") — and wants something done to it or produced from it. Also trigger for cleaning or restructuring messy tabular data files (malformed rows, misplaced headers, junk data) into proper spreadsheets. The deliverable must be a spreadsheet file. Do NOT trigger when the primary deliverable is a Word document, HTML report, standalone Python script, database pipeline, or Google Sheets API integration, even if tabular data is involved. | Stars: 196k | Updated | Claude CodeCodex | Verified | Install |
| shipping-and-launchaddyosmani/agent-skillsPrepares production launches. Use when preparing to deploy to production, or when asking what needs to be in place before shipping. Use when you need a pre-launch checklist, when setting up monitoring, when planning a staged rollout, or when you need a rollback strategy. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| childhoodthedotmack/claude-memBabysit a shipped release after merge, publication, or deployment. Verify published packages, production behavior, installed plugins, and late CI or user reports until the observation window is clean; use babysit for PR readiness before release. | Stars: 117k | Updated | Claude CodeCodex | Unverified | Install |