/write-query - Write Optimized SQL

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Usage

/write-query <description of what data you need>

Workflow

1. Understand the Request

• Output columns: What fields should the result include?
• Filters: What conditions limit the data (time ranges, segments, statuses)?
• Aggregations: Are there GROUP BY operations, counts, sums, averages?
• Joins: Does this require combining multiple tables?
• Ordering: How should results be sorted?
• Limits: Is there a top-N or sample requirement?

2. Determine SQL Dialect

• PostgreSQL (including Aurora, RDS, Supabase, Neon)
• Snowflake
• BigQuery (Google Cloud)
• Redshift (Amazon)
• Databricks SQL
• MySQL (including Aurora MySQL, PlanetScale)
• SQL Server (Microsoft)
• DuckDB
• SQLite
• Other (ask for specifics)

3. Discover Schema (If Warehouse Connected)

1. Search for relevant tables based on the user's description
2. Inspect column names, types, and relationships
3. Check for partitioning or clustering keys that affect performance
4. Look for pre-built views or materialized views that might simplify the query

4. Write the Query

• Use CTEs (WITH clauses) for readability when queries have multiple logical steps
• One CTE per logical transformation or data source
• Name CTEs descriptively (e.g.,

  daily_signups

  ,

  active_users

  ,

  revenue_by_product

  )

• Never use

  SELECT *

  in production queries -- specify only needed columns
• Filter early (push WHERE clauses as close to the base tables as possible)
• Use partition filters when available (especially date partitions)
• Prefer

  EXISTS

  over

  IN

  for subqueries with large result sets
• Use appropriate JOIN types (don't use LEFT JOIN when INNER JOIN is correct)
• Avoid correlated subqueries when a JOIN or window function works
• Be mindful of exploding joins (many-to-many)

• Add comments explaining the "why" for non-obvious logic
• Use consistent indentation and formatting
• Alias tables with meaningful short names (not just

  a

  ,

  b

  ,

  c

  )
• Put each major clause on its own line

• Apply dialect-specific syntax and functions (see

  sql-queries

  skill for details)
• Use dialect-appropriate date functions, string functions, and window syntax
• Note any dialect-specific performance features (e.g., Snowflake clustering, BigQuery partitioning)

5. Present the Query

1. The complete query in a SQL code block with syntax highlighting
2. Brief explanation of what each CTE or section does
3. Performance notes if relevant (expected cost, partition usage, potential bottlenecks)
4. Modification suggestions -- how to adjust for common variations (different time range, different granularity, additional filters)

6. Offer to Execute

Examples

/write-query Count of orders by status for the last 30 days

/write-query Cohort retention analysis -- group users by their signup month, then show what percentage are still active (had at least one event) at 1, 3, 6, and 12 months after signup

/write-query We have a 500M row events table partitioned by date. Find the top 100 users by event count in the last 7 days with their most recent event type.

Tips

• Mention your SQL dialect upfront to get the right syntax immediately
• If you know the table names, include them -- otherwise Claude will help you find them
• Specify if you need the query to be idempotent (safe to re-run) or one-time
• For recurring queries, mention if it should be parameterized for date ranges