optimizing-query-text

What this skill does

# Optimize Query from SQL Text

## OUTPUT FORMAT

Return ONLY the optimized SQL query. No markdown formatting, no explanations, no bullet points - just pure SQL that can be executed directly in Snowflake.

## CRITICAL: Semantic Preservation Rules

**The optimized query MUST return IDENTICAL results to the original.**

Before returning ANY optimization, verify:
- **Same columns**: Exact same columns in exact same order with exact same aliases
- **Same rows**: Filter conditions must be semantically equivalent
- **Same ordering**: Preserve `ORDER BY` exactly as written
- **Same limits**: If original has `LIMIT N`, keep `LIMIT N`. If no LIMIT, do NOT add one.

**If you cannot guarantee identical results, return the original query unchanged.**

---

## Pattern 1: Function on Filter Column

**Problem**: Functions on columns in WHERE clause prevent partition pruning and index usage.

### CAN Fix

| Original | Optimized | Why Safe |
|----------|-----------|----------|
| `WHERE DATE(ts) = '2024-01-01'` | `WHERE ts >= '2024-01-01' AND ts < '2024-01-02'` | Equivalent range |
| `WHERE YEAR(dt) = 2024` | `WHERE dt >= '2024-01-01' AND dt < '2025-01-01'` | Equivalent range |
| `WHERE MONTH(dt) = 3 AND YEAR(dt) = 2024` | `WHERE dt >= '2024-03-01' AND dt < '2024-04-01'` | Equivalent range |
| `WHERE DATE(ts) >= '2024-01-01' AND DATE(ts) < '2024-02-01'` | `WHERE ts >= '2024-01-01' AND ts < '2024-02-01'` | Same boundaries |
| `WHERE YEAR(dt) BETWEEN 1995 AND 1996` | `WHERE dt >= '1995-01-01' AND dt < '1997-01-01'` | Equivalent range |

### CANNOT Fix

| Pattern | Why Not |
|---------|---------|
| `WHERE YEAR(dt) IN (SELECT year FROM ...)` | Dynamic values, cannot precompute range |
| `WHERE DATE(ts) = DATE(other_col)` | Comparing two columns, both need function |
| `WHERE EXTRACT(DOW FROM dt) = 1` | Day-of-week has no contiguous range |
| `WHERE DATE_TRUNC('month', dt) = '2024-01-01'` in GROUP BY | Needed for grouping logic |
| `SELECT YEAR(dt) AS yr ... GROUP BY YEAR(dt)` | Function in SELECT/GROUP BY is fine, only filter matters |

---

## Pattern 2: Function on JOIN Column

**Problem**: Functions on JOIN columns prevent hash joins, forcing slower nested loop joins.

### CAN Fix

| Original | Optimized | Why Safe |
|----------|-----------|----------|
| `ON CAST(a.id AS VARCHAR) = CAST(b.id AS VARCHAR)` | `ON a.id = b.id` | If both are same type (e.g., INTEGER) |
| `ON UPPER(a.code) = UPPER(b.code)` | `ON a.code = b.code` | If data is already consistently cased |
| `ON TRIM(a.name) = TRIM(b.name)` | `ON a.name = b.name` | If data has no leading/trailing spaces |

### CANNOT Fix

| Pattern | Why Not |
|---------|---------|
| `ON CAST(a.id AS VARCHAR) = b.string_id` | Types genuinely differ, CAST required |
| `ON DATE(a.timestamp) = b.date_col` | Different granularity, DATE() required |
| `ON UPPER(a.code) = b.code` | If b.code might have different case |
| `ON a.id = b.id + 1` | Arithmetic transformation, cannot remove |

---

## Pattern 3: NOT IN Subquery

**Problem**: `NOT IN` has poor performance and unexpected NULL behavior.

### CAN Fix

| Original | Optimized | Why Safe |
|----------|-----------|----------|
| `WHERE id NOT IN (SELECT id FROM t WHERE ...)` | `WHERE NOT EXISTS (SELECT 1 FROM t WHERE t.id = main.id AND ...)` | Equivalent when subquery column is NOT NULL |
| `WHERE id NOT IN (SELECT id FROM t)` where id has NOT NULL constraint | `WHERE NOT EXISTS (SELECT 1 FROM t WHERE t.id = main.id)` | NOT NULL guarantees equivalence |

### CANNOT Fix

| Pattern | Why Not |
|---------|---------|
| `WHERE id NOT IN (SELECT nullable_col FROM t)` | If subquery returns NULL, NOT IN returns no rows; NOT EXISTS doesn't |
| `WHERE (a, b) NOT IN (SELECT x, y FROM t)` | Multi-column NOT IN has complex NULL semantics |

**Key Rule**: Only convert NOT IN to NOT EXISTS if you can verify the subquery column cannot be NULL.

---

## Pattern 4: Repeated Subquery

**Problem**: Same subquery executed multiple times causes redundant scans.

### CAN Fix

| Original | Optimized |
|----------|-----------|
| Subquery appears 2+ times identically | Extract to CTE, reference CTE multiple times |
| Same aggregation used in multiple places | Compute once in CTE |

### CANNOT Fix

| Pattern | Why Not |
|---------|---------|
| Correlated subquery (references outer table) | Each execution is different, cannot cache |
| Subqueries with different filters | Not actually the same subquery |
| Subquery in SELECT that depends on current row | Correlation prevents extraction |

---

## Pattern 5: Implicit Comma Joins

**Problem**: Comma-separated tables in FROM clause are harder to read and optimize.

### CAN Fix - Always

Convert `FROM a, b, c WHERE a.id = b.id AND b.id = c.id` to explicit JOIN syntax.

This is always safe - just restructuring, no semantic change.

---

## UNSAFE Optimizations (NEVER apply)

- **UNION to UNION ALL**: UNION deduplicates rows, UNION ALL does not - different results
- **Changing window functions**: Do not modify `SUM(SUM(x)) OVER(...)` or similar nested aggregates
- **Adding redundant filters**: Do not add filters in JOIN ON if same filter exists in WHERE
- **Changing column names**: Copy column names EXACTLY from original - do not "simplify" or rename
- **Changing column aliases**: Keep all aliases exactly as original
- **Adding early filtering in JOINs**: If a filter is in WHERE, do not duplicate it in JOIN ON clause

---

## Principles

1. **Minimal changes**: Make the fewest changes necessary. Simpler optimizations are more reliable.
2. **Preserve structure**: Keep subqueries, CTEs, and overall query structure unless there's a clear benefit.
3. **When in doubt, don't**: If unsure whether a change preserves semantics, skip it.
4. **Copy exactly**: Column names, table aliases, and expressions should be copied character-for-character.

---

## Priority Order

1. **Date/time functions on filter columns** - Highest impact
2. **Implicit joins to explicit JOIN** - Always safe, improves readability
3. **NOT IN to NOT EXISTS** - Only if NULL-safe

---

## Requirements

- **Results must be identical**: Same rows, same columns, same order
- **Valid Snowflake SQL**: Output must execute without errors in Snowflake