Skip to content

hoavdc/CodexKit / skills/codexkit-sql-query-builder

codexkit-sql-query-builder

Unverified

Build 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.

FileKindSize
SKILL.mdskill md6.0 KB
agents/openai.yamlconfig168 B
examples/common-mistakes.mdmarkdown842 B
examples/good-output.mdmarkdown1.2 KB
scripts/query-validator.sqlother3.6 KB
verification/checklist.mdmarkdown1.4 KB
---
name: codexkit-sql-query-builder
description: Build 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.
version: 1.0.0
category: data
---

# 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:

```sql
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

```markdown
## 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
writing-skillsobra/superpowersUse when creating new skills, editing existing skills, or verifying skills work before deploymentStars: 318kUpdated Claude CodeCodexUnverifiedInstall
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: 196kUpdated Claude CodeCodexVerifiedInstall
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: 118kUpdated Claude CodeCodexVerifiedInstall
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: 117kUpdated Claude CodeCodexUnverifiedInstall