sql-patterns

0xDarkMatter's avatarfrom 0xDarkMatter

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.

⭐3stars🔀0forks📁View on GitHub🕐Updated Dec 27, 2025
[SQL and Databases]

When & Why to Use This Skill

This Claude skill serves as a comprehensive quick-reference guide for SQL development and database optimization. It provides ready-to-use templates for complex queries, including Common Table Expressions (CTEs), advanced window functions, and efficient join patterns. By offering best practices for indexing and identifying common anti-patterns, it helps developers and data analysts write cleaner, more performant SQL code while accelerating the troubleshooting of data retrieval issues.

Use Cases

  • Complex Query Structuring: Use chained CTEs to break down intricate data transformations into readable, maintainable logical blocks.
  • Advanced Data Analytics: Implement sophisticated reporting features like running totals, moving averages, and ranking using optimized window function syntax.
  • Performance Optimization: Identify and fix slow queries by applying the correct indexing strategies (B-tree, Hash, GIN) and replacing inefficient anti-patterns like 'SELECT *' or 'NOT IN' with NULLs.
  • Scalable Pagination: Transition from slow OFFSET/LIMIT pagination to high-performance keyset-based pagination for large-scale web applications.
  • Database Schema Design: Reference best practices for join types and indexing to ensure efficient data relationships and fast retrieval speeds.
namesql-patterns
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."
allowed-tools"Read Write"

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