Write Query Skill

Translate natural language data requests into correct, optimized SQL. Handle ambiguity, apply business context, and produce queries that are readable, performant, and correct.

Workflow

Step 1: Parse the Natural Language Request

Break the request into SQL components:

Step 2: Identify Ambiguities

Common ambiguities to resolve before writing SQL:

• "users" -- Do they mean all-time users, active users, registered users?
• "revenue" -- Gross or net? Including or excluding refunds?
• "last month" -- Calendar month or rolling 30 days?
• "active" -- Logged in? Made a purchase? Any event?
• "new" -- First appearance ever, or first in this period?
• "conversion rate" -- Visitors to signup? Signup to purchase? What denominator?

If context is available (from a data context skill or conversation), use it. Otherwise, state the assumption explicitly in a comment.

Step 3: Build the Query

Follow this structure:

-- Question: [Restate the natural language question]
-- Assumptions: [State any assumptions made]
-- Dialect: [Snowflake/BigQuery/PostgreSQL/etc.]

WITH
-- Step 1: Get the base data with filters
source_data AS (
    SELECT
        relevant_columns
    FROM schema.table
    WHERE standard_filters
      AND date_filter
),

-- Step 2: Intermediate transformations
transformed AS (
    SELECT
        grouping_columns,
        aggregations
    FROM source_data
    GROUP BY grouping_columns
),

-- Step 3: Final calculations and formatting
final AS (
    SELECT
        columns,
        calculated_fields,
        window_functions
    FROM transformed
)

SELECT *
FROM final
ORDER BY sort_column
;

Step 4: Handle Common Query Patterns

Metric Over Time

-- "Show me daily revenue for the last 30 days"
SELECT
    DATE_TRUNC('day', order_date) AS day,
    SUM(amount) AS revenue,
    COUNT(DISTINCT customer_id) AS customers,
    SUM(amount) / NULLIF(COUNT(DISTINCT customer_id), 0) AS revenue_per_customer
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
  AND status = 'completed'
GROUP BY 1
ORDER BY 1
;

Ranking / Top-N

-- "Top 10 products by revenue this quarter"
SELECT
    product_name,
    SUM(quantity) AS units_sold,
    SUM(revenue) AS total_revenue,
    RANK() OVER (ORDER BY SUM(revenue) DESC) AS revenue_rank
FROM order_items oi
JOIN products p ON oi.product_id = p.id
WHERE order_date >= DATE_TRUNC('quarter', CURRENT_DATE)
GROUP BY product_name
ORDER BY total_revenue DESC
LIMIT 10
;

Period-over-Period Comparison

-- "Compare this month to last month by channel"
WITH monthly AS (
    SELECT
        channel,
        DATE_TRUNC('month', order_date) AS month,
        SUM(revenue) AS revenue
    FROM orders
    WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '2 months'
    GROUP BY 1, 2
)
SELECT
    channel,
    MAX(CASE WHEN month = DATE_TRUNC('month', CURRENT_DATE) THEN revenue END) AS current_month,
    MAX(CASE WHEN month = DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month' THEN revenue END) AS prior_month,
    current_month - prior_month AS absolute_change,
    (current_month - prior_month) / NULLIF(prior_month, 0) AS pct_change
FROM monthly
GROUP BY 1
ORDER BY current_month DESC
;

Cohort Analysis

-- "Show monthly retention by signup cohort"
WITH cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(created_at)) AS cohort_month
    FROM users
    GROUP BY 1
),
activity AS (
    SELECT DISTINCT
        user_id,
        DATE_TRUNC('month', event_date) AS active_month
    FROM events
)
SELECT
    c.cohort_month,
    DATEDIFF('month', c.cohort_month, a.active_month) AS months_since_signup,
    COUNT(DISTINCT a.user_id) AS active_users,
    COUNT(DISTINCT a.user_id)::FLOAT / MAX(cohort_size.cnt) AS retention_rate
FROM cohorts c
JOIN activity a ON c.user_id = a.user_id AND a.active_month >= c.cohort_month
JOIN (
    SELECT cohort_month, COUNT(*) AS cnt FROM cohorts GROUP BY 1
) cohort_size ON c.cohort_month = cohort_size.cohort_month
GROUP BY 1, 2
ORDER BY 1, 2
;

Funnel Analysis

-- "What's our signup funnel conversion?"
WITH funnel AS (
    SELECT
        COUNT(DISTINCT CASE WHEN step = 'page_view' THEN user_id END) AS page_views,
        COUNT(DISTINCT CASE WHEN step = 'signup_start' THEN user_id END) AS signup_starts,
        COUNT(DISTINCT CASE WHEN step = 'email_verified' THEN user_id END) AS email_verified,
        COUNT(DISTINCT CASE WHEN step = 'first_action' THEN user_id END) AS first_action
    FROM events
    WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    page_views,
    signup_starts,
    signup_starts::FLOAT / NULLIF(page_views, 0) AS start_rate,
    email_verified,
    email_verified::FLOAT / NULLIF(signup_starts, 0) AS verify_rate,
    first_action,
    first_action::FLOAT / NULLIF(email_verified, 0) AS activation_rate,
    first_action::FLOAT / NULLIF(page_views, 0) AS overall_conversion
FROM funnel
;

Step 5: Validate the Query

Before delivering, check:

1. Does the GROUP BY match the SELECT? Every non-aggregated column must be in GROUP BY.
2. Are joins correct? Check for fan-out (1:many producing duplicates) or dropped rows (inner vs left).
3. Are filters in the right place? WHERE vs HAVING, and applied in the right CTE.
4. Is division by zero handled? Wrap denominators with NULLIF(x, 0).
5. Are date boundaries correct? >= start and < end for date ranges (avoid BETWEEN for timestamps).
6. Does the grain match the question? If they asked "per user" but you grouped by user and date, that is wrong.

Output Format

-- Question: [Natural language question restated]
-- Dialect: [Target SQL dialect]
-- Tables: [Tables used]
-- Assumptions: [Any assumptions made]
-- Expected output: [Description of columns and grain]

[The SQL query]

Best Practices

1. Restate the question. Always confirm what you think is being asked before writing SQL.
2. Name CTEs clearly. daily_revenue not cte1. A reader should understand the query from CTE names alone.
3. Comment non-obvious logic. Explain business rules, not SQL syntax.
4. Handle edge cases. What happens with nulls? What if the date range has no data?
5. Use consistent formatting. One clause per line, aligned keywords, indented subqueries.
6. Prefer explicit to implicit. LEFT JOIN not LEFT OUTER JOIN ambiguity. CAST over implicit coercion.
7. Test incrementally. Run each CTE independently before combining.
8. Include a sanity check. Add a CTE that counts rows or sums a key metric to verify the result makes sense.
9. Consider performance. Note when a query might be slow and suggest optimizations (partitioning, pre-aggregation).
10. Provide alternatives. If there are multiple valid interpretations, show the SQL for each.