Skip to content
Skills Library
Data & analytics · Learning & explanation

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.

Repository
claude-mods ↗
Licence
MIT
Last repository change
15.10.2026

Assessment and security

95/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content30 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
5
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 15.10.2026, without references to retired models or APIs.

Licence

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

  1. CTE (Common Table Expressions)
  2. Window Functions (Quick Reference)
  3. JOIN Reference
  4. Pagination
  5. Transaction Isolation Levels
  6. Index Quick Reference
  7. Anti-Patterns
  8. Additional Resources
Original SKILL.md headings · 115 lines, 504 words
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",…

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.