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;

Transaction Isolation Levels

Level	Dirty Read	Non-Repeatable Read	Phantom Read	Use
`READ UNCOMMITTED`	Possible	Possible	Possible	Rarely (PostgreSQL treats as READ COMMITTED)
`READ COMMITTED`	No	Possible	Possible	Default in most databases
`REPEATABLE READ`	No	No	Possible*	Consistent multi-statement reads
`SERIALIZABLE`	No	No	No	Critical invariants (retry on serialization failure)

*PostgreSQL's REPEATABLE READ also prevents phantoms via snapshot isolation.

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... critical operations ...
COMMIT;  -- be prepared to retry on serialization failure

Keep the default READ COMMITTED globally; raise the level per-transaction only where the logic requires it.

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

sql-ops

SQL Patterns

CTE (Common Table Expressions)

Chained CTEs