codexkit-sql-query-builder

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;