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
1---2name: sql-patterns3description: "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."4allowed-tools: "Read Write"5---67# SQL Patterns89Quick reference for common SQL patterns.1011## CTE (Common Table Expressions)1213```sql14WITH active_users AS (15 SELECT id, name, email16 FROM users17 WHERE status = 'active'18)19SELECT * FROM active_users WHERE created_at > '2024-01-01';20```2122### Chained CTEs2324```sql25WITH26 active_users AS (27 SELECT id, name FROM users WHERE status = 'active'28 ),29 user_orders AS (30 SELECT user_id, COUNT(*) as order_count31 FROM orders GROUP BY user_id32 )33SELECT u.name, COALESCE(o.order_count, 0) as orders34FROM active_users u35LEFT JOIN user_orders o ON u.id = o.user_id;36```3738## Window Functions (Quick Reference)3940| Function | Use |41|----------|-----|42| `ROW_NUMBER()` | Unique sequential numbering |43| `RANK()` | Rank with gaps (1, 2, 2, 4) |44| `DENSE_RANK()` | Rank without gaps (1, 2, 2, 3) |45| `LAG(col, n)` | Previous row value |46| `LEAD(col, n)` | Next row value |47| `SUM() OVER` | Running total |48| `AVG() OVER` | Moving average |4950```sql51SELECT52 date,53 revenue,54 LAG(revenue, 1) OVER (ORDER BY date) as prev_day,55 SUM(revenue) OVER (ORDER BY date) as running_total56FROM daily_sales;57```5859## JOIN Reference6061| Type | Returns |62|------|---------|63| `INNER JOIN` | Only matching rows |64| `LEFT JOIN` | All left + matching right |65| `RIGHT JOIN` | All right + matching left |66| `FULL JOIN` | All rows, NULL where no match |6768## Pagination6970```sql71-- OFFSET/LIMIT (simple, slow for large offsets)72SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 40;7374-- Keyset (fast, scalable)75SELECT * FROM products WHERE id > 42 ORDER BY id LIMIT 20;76```7778## Index Quick Reference7980| Index Type | Best For |81|------------|----------|82| B-tree | Range queries, ORDER BY |83| Hash | Exact equality only |84| GIN | Arrays, JSONB, full-text |85| Covering | Avoid table lookup |8687## Anti-Patterns8889| Mistake | Fix |90|---------|-----|91| `SELECT *` | List columns explicitly |92| `WHERE YEAR(date) = 2024` | `WHERE date >= '2024-01-01'` |93| `NOT IN` with NULLs | Use `NOT EXISTS` |94| N+1 queries | Use JOIN or batch |9596## Additional Resources9798For detailed patterns, load:99- `./references/window-functions.md` - Complete window function patterns100- `./references/indexing-strategies.md` - Index types, covering indexes, optimization
aiskillstore