sql-patterns

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