sql-ops
/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.
- Author
- 0xDarkMatter ↗
- Repository
- claude-mods ↗
- Licence
- MIT
- Last repository change
- 15.10.2026
Assessment and security
- Files in this package
- 5
- Scripts
- 0
- HTML comments
- 0
- URLs in SKILL.md
- 0
No signals detected. Repository changed on 15.10.2026, without references to retired models or APIs.
MIT, open: use and modify while retaining author attribution.
What it does and when to use it
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.
Original author 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.
How it works
- CTE (Common Table Expressions)
- Window Functions (Quick Reference)
- JOIN Reference
- Pagination
- Transaction Isolation Levels
- Index Quick Reference
- Anti-Patterns
- Additional Resources
SKILL.md preview
---
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
Files in this package (5)
- SKILL.md 3 KB
- assets/.gitkeep 1 KB
- references/indexing-strategies.md 4 KB
- references/window-functions.md 7 KB
- scripts/.gitkeep 1 KB
Versions
The database has no other skills named sql-ops.
Database snapshot: 13.11.2026 · package version 0b4805cdfe6d (SHA-256 digest of the entire package)
Downloads in the last 30 days: 0 · ranking
Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.
Similar skills
- 100sql
Expert SQL assistance covering ANSI SQL, CTEs, Window Functions, aggregation, indexing, and query optimization. Use when writing complex analytical queries, tuning query execution plans, or designing databases. - 100sql-query-generation
Generate SQL queries from natural-language requirements using SELECT, JOIN, GROUP BY, window functions, CTEs, and subqueries. Use when the user needs a new query from a business question or schema; use query-optimization when an existing query or execution plan is slow. - 100sql-analyzer
Activates SQLAnalyzer for advanced SQL optimization, query analysis, and database performance tuning. Use when you need to optimize a slow query using EXPLAIN plans, rewrite subqueries as CTEs or window functions, design complex analytical queries, identify missing indexes,… - 100sql
Use when writing or optimizing SQL. Covers query planning, indexing strategy, window functions, CTEs, transaction isolation, and reading EXPLAIN output. Trigger on "this query is slow", "how do i read this explain plan", "write a window function for running totals", "what… - 100sql-ansi-advanced-window-functions
Use this skill when writing complex analytical SQL using ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG/LEAD, and Common Table Expressions. - 100sql-advanced
Advanced SQL patterns including CTEs, window functions, recursive queries, query optimization, and EXPLAIN analysis. Use for complex query writing and performance tuning. USE WHEN: user mentions "CTE", "window functions", "recursive queries", "EXPLAIN", "query optimization",…