sql-ops ยท diff
git:20260410.d221136 to git:20260610.e29f196
19 added, 0 removed. Audit A to A.
---
name: sql-ops
description: "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."
license: MIT
allowed-tools: "Read Write"
metadata:
author: claude-mods
related-skills: postgres-ops, sqlite-ops
---
# 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 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.
+
+ ```sql
+ 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