SQL Patterns

SQL Patterns

Quick reference for common SQL patterns.

CTE (Common Table Expressions)

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

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 |

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

-- 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