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