liangxingguang/sql-analysis

SQL Analysis Skill

Repeatable SOP for turning a business question into a verifiable SQL answer.

Steps

1. Restate the question as a metric. In one sentence, write: "<metric> by <grouping> over <time window>, filtered by <filter>". If any of those four (metric / grouping / window / filter) is missing or ambiguous, ask one clarifying question and stop. Do not guess.

2. Locate the source. Decide where the data lives:

   • Look in knowledge/ first for any schema notes, data-dictionary entries, or prior query examples uploaded by the user.
   • If the right table is not obvious, delegate to the data-explorer sub-agent with the metric definition as the prompt — its job is to identify the canonical source.

3. Draft the query. Write the SQL in a fenced sql block. Conventions:

   • Always include the time window in a WHERE clause — never query the full history "just in case".
   • Always SELECT an explicit column list — never SELECT * in an answer.
   • Use CTEs (WITH x AS (...)) over nested subqueries for anything beyond two levels of nesting.
   • Comment any non-obvious filter (-- excludes internal test accounts).

4. Validate before reporting. Run the query and check:

   • Row count is in the ballpark you expected (1 row, 10 rows, ~30 daily buckets, etc.). A surprising row count is almost always a bug — investigate.
   • No NULLs in the grouping column unless that is the intended cohort.
   • At least one numeric sanity check: a known total, a known reference value, or a min/max range that matches reality.
   • If anything looks off, do not report the number — fix the query first.

5. Write the report. Use this exact structure:

   ## Answer
   <one-sentence direct answer with the headline number(s)>
   
   ## Result
   <small markdown table — at most ~15 rows; for longer results, summarise
   and offer to render a chart or attach the full CSV>
   
   ## Query
   ```sql
   <the exact query you ran>
   

   Sources & validation

   • Table(s): schema.table_name (row count, freshness if known)
   • Validation: <which sanity checks you ran and the result>
   • Caveats: <known data quality issues, missing dates, etc.>

Anti-patterns

• ❌ Reporting a number without the query that produced it.
• ❌ Using LIMIT N to "make the output fit" without explaining what got cut.
• ❌ Reading a single row and reporting it as a trend.
• ❌ Inventing a table or column name. If unsure, delegate to data-explorer.

When to delegate

• The user's question requires probing several candidate tables / sources before any query can be written → spawn data-explorer.
• The user wants a polished written deliverable summarising several analyses (e.g. "weekly health report") → spawn report-writer after you have the underlying numbers ready.