# SQL Generation Instructions

## CRITICAL: Output Format
Generate **ONLY** the SQL query. No explanations. No markdown.

## HIERARCHICAL DECISION FRAMEWORK

### Level 1: Evidence is Law
When evidence provides information, use it EXACTLY:
- `X refers to ColumnName` → Use ColumnName
- `DIVIDE(A,B)` → `CAST(A AS REAL) / NULLIF(B, 0)`
- `X = 'value'` → Use this exact value
- Evidence overrides ALL other rules

### Level 2: Schema Reference
The analysis provides EXACT column locations:
- Find each column in "SCHEMA VALIDATION" section
- Use the exact table.column shown
- If not listed, it doesn't exist - never guess

### Level 3: Value Matching
Use EXACT values from "VALUE SAMPLES":
- Match case precisely ('Active' ≠ 'active')
- Check for empty string '' vs NULL
- Use the exact format shown

### Level 4: Query Patterns
If provided, use patterns from "QUERY PATTERNS":
- Find the matching pattern
- Replace placeholders with your values
- Use the exact syntax shown

## STEP-BY-STEP PROCESS

### Step 1: Parse Evidence
```
Evidence mappings to extract:
- Column names: "X refers to Y"
- Formulas: "calculation = FORMULA"
- Values: "status = 'active'"
- Operators: "greater than" → >
```

### Step 2: Build Query Components

**SELECT clause:**
- "What is X?" → `SELECT X`
- "List Y" → `SELECT Y`
- "How many?" → `SELECT COUNT(*)`
- Return ONLY requested columns

**FROM clause:**
- Use table from schema reference
- Apply aliases for joins: `table1 t1`

**WHERE clause:**
- Use exact values from samples
- Apply evidence conditions
- Handle NULLs properly: `IS NULL` not `= NULL`

**JOINs (when needed):**
```sql
FROM table1 t1
JOIN table2 t2 ON t1.foreign_key = t2.primary_key
```

## COMMON PATTERNS

### Aggregations
```sql
COUNT(*)                        -- Total rows
COUNT(DISTINCT column)          -- Unique values
SUM(column)                     -- Total
AVG(column)                     -- Average
MAX(column), MIN(column)        -- Extremes
```

### Percentages
```sql
-- Standard percentage
CAST(part AS REAL) * 100 / NULLIF(whole, 0)

-- Conditional percentage
CAST(COUNT(CASE WHEN condition THEN 1 END) AS REAL) * 100 / NULLIF(COUNT(*), 0)
```

### Top-N
```sql
SELECT column
FROM table
ORDER BY metric DESC
LIMIT N
```

### Conditional Counting
```sql
SELECT COUNT(CASE WHEN condition THEN 1 END)
FROM table
```

## SPECIAL HANDLING

### Table/Column Names
- With spaces: `"Table Name"` or `` `Table Name` ``
- With hyphens: `` `table-name` ``
- Regular: just use the name

### String Values
- Single quotes: `'value'`
- Escape quotes: `'O''Brien'`
- Case matters - use exact case from samples

### NULL Safety
- Division: `NULLIF(denominator, 0)`
- Comparisons: `IS NULL` or `IS NOT NULL`
- COALESCE for defaults: `COALESCE(column, 0)`

## VALIDATION CHECKLIST

Before outputting:
1. ✓ Evidence applied exactly?
2. ✓ Columns exist in schema?
3. ✓ Values match samples?
4. ✓ Only requested columns returned?
5. ✓ Proper NULL handling?
6. ✓ Query ends with semicolon?

## SQLITE SPECIFICS

- String concat: `||` not CONCAT
- LIKE is case-insensitive
- Date functions: `date()`, `strftime()`
- Type conversion: `CAST(column AS type)`

## CRITICAL RULES

1. **Never guess** - If column not in schema, it doesn't exist
2. **Exact matching** - Values must match samples exactly
3. **Evidence first** - Evidence overrides general rules
4. **Simple is better** - Use basic SQL when possible
5. **Return only asked** - No extra columns ever

## ERROR PREVENTION

### Common Mistakes to Avoid:
- ✗ Guessing column names
- ✗ Wrong case in string values
- ✗ = NULL instead of IS NULL
- ✗ Missing NULLIF in division
- ✗ Adding unrequested columns
- ✗ Wrong table for column

### If Unsure:
- Check schema reference again
- Use exact value from samples
- Follow evidence literally
- Keep query simple