# SQL Optimization

> Skill: SQL Optimization

- Skill: `sawrus/sql-optimization` (Agent Skill)
- Install (CLI): `npx skillmds@latest add sawrus/sql-optimization`
- Raw SKILL.md: https://api.skillmd.com/api/skills/sawrus/sql-optimization/raw
- Safety review: pending (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: sawrus (https://skillmd.com/u/sawrus)
- Updated: 2026-09-21
- Page: https://skillmd.com/skills/sawrus/sql-optimization

---

# Skill: SQL Optimization

## When to load

When writing complex queries, optimizing slow queries, designing indexes, or reviewing query performance.

## Core Patterns

```sql
-- ✅ Column projection — never SELECT *
SELECT user_id, created_at, total_amount FROM orders WHERE created_at >= '2026-01-01';

-- ✅ Partition pruning — filter on partition column first
SELECT user_id, total_amount
FROM orders
WHERE order_date = '2026-01-15'  -- partition filter first
  AND status = 'completed';

-- ❌ Function on indexed column defeats index
WHERE DATE(created_at) = '2026-01-15'
-- ✅ Range comparison preserves index
WHERE created_at >= '2026-01-15' AND created_at < '2026-01-16'
```

## Window Functions (prefer over self-joins)

```sql
SELECT
    user_id, order_date, amount,
    SUM(amount) OVER (
        PARTITION BY user_id ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS recency_rank
FROM orders;
```

## Snowflake-Specific

```sql
-- Cluster keys on large tables (> 1 TB)
ALTER TABLE orders CLUSTER BY (order_date, status);

-- COPY INTO for bulk loads — never row-by-row INSERT
COPY INTO orders FROM @my_stage/orders/
FILE_FORMAT = (TYPE = PARQUET) MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;
```