sql-ops
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.
Install
npx skills add https://github.com/0xDarkMatter/claude-mods/tree/main/skills/sql-ops
claude plugin marketplace add https://llmmart.ai/marketplace.json && claude plugin install 0xdarkmatter-claude-mods@llmmart
git clone https://github.com/0xDarkMatter/claude-mods.git
The skills CLI installs just this skill, for any of its supported agents. Claude Code installs the whole 0xdarkmatter/claude-mods collection as a plugin from our marketplace. Git is the plain clone.
Skill manifest
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;
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
Files (claude-mods)
-
assets
-
.gitkeep 0 B · in bundle
-
-
references
-
indexing-strategies.md 3.9 KB
Loading… -
window-functions.md 6.9 KB
Loading…
-
-
scripts
-
.gitkeep 0 B · in bundle
-
-
SKILL.md 3.4 KB
--- 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
Comments (0)
Sign in to join the conversation.
Reviews (0)
No reviews yet.
No comments yet.