Sql Patterns

---
name: sql-patterns
description: "Quick reference for common SQL patterns, CTEs, window functions, and indexing strategies. Triggers on: sql patterns, cte example, window functions, sql join, index strategy, pagination sql."
allowed-tools: "Read Write"
---

# SQL Patterns

Quick reference for common SQL patterns.

## CTE (Common Table Expressions)

```sql
WITH active_users AS (
    SELECT id, name, email
    FROM users
    WHERE status = 'active'
)
SELECT * FROM active_users WHERE created_at > '2024-01-01';
```

### Chained CTEs

```sql
WITH
    active_users AS (
        SELECT id, name FROM users WHERE status = 'active'
    ),
    user_orders AS (
        SELECT user_id, COUNT(*) as order_count
        FROM orders GROUP BY user_id
    )
SELECT u.name, COALESCE(o.order_count, 0) as orders
FROM active_users u
LEFT JOIN user_orders o ON u.id = o.user_id;
```

## Window Functions (Quick Reference)

| Function | Use |
|----------|-----|
| `ROW_NUMBER()` | Unique sequential numbering |
| `RANK()` | Rank with gaps (1, 2, 2, 4) |
| `DENSE_RANK()` | Rank without gaps (1, 2, 2, 3) |
| `LAG(col, n)` | Previous row value |
| `LEAD(col, n)` | Next row value |
| `SUM() OVER` | Running total |
| `AVG() OVER` | Moving average |

```sql
SELECT
    date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY date) as prev_day,
    SUM(revenue) OVER (ORDER BY date) as running_total
FROM daily_sales;
```

## JOIN Reference

| Type | Returns |
|------|---------|
| `INNER JOIN` | Only matching rows |
| `LEFT JOIN` | All left + matching right |
| `RIGHT JOIN` | All right + matching left |
| `FULL JOIN` | All rows, NULL where no match |

## Pagination

```sql
-- OFFSET/LIMIT (simple, slow for large offsets)
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 40;

-- Keyset (fast, scalable)
SELECT * FROM products WHERE id > 42 ORDER BY id LIMIT 20;
```

## Index Quick Reference

| Index Type | Best For |
|------------|----------|
| B-tree | Range queries, ORDER BY |
| Hash | Exact equality only |
| GIN | Arrays, JSONB, full-text |
| Covering | Avoid table lookup |

## Anti-Patterns

| Mistake | Fix |
|---------|-----|
| `SELECT *` | List columns explicitly |
| `WHERE YEAR(date) = 2024` | `WHERE date >= '2024-01-01'` |
| `NOT IN` with NULLs | Use `NOT EXISTS` |
| N+1 queries | Use JOIN or batch |

## Additional Resources

For detailed patterns, load:
- `./references/window-functions.md` - Complete window function patterns
- `./references/indexing-strategies.md` - Index types, covering indexes, optimization