sql-patterns_skill

Overview

This skill is a compact reference for common SQL patterns, including CTEs, window functions, joins, pagination, and indexing strategies. It delivers clear examples and quick rules-of-thumb to speed up query writing and optimization. Use it to validate patterns, avoid anti-patterns, and choose appropriate indexes. The content is focused on practical, copy-pasteable snippets.

How this skill works

The skill provides short, annotated examples for common tasks: building CTEs, chaining CTEs, using window functions, and applying joins and pagination. It lists index types and their ideal use cases, plus common anti-patterns with concise fixes. You can quickly look up the right pattern, adapt the SQL sample, and apply best practices to improve performance and readability.

When to use it

• Composing complex queries that benefit from CTEs or chained CTEs
• Implementing analytics or running row-based calculations with window functions
• Choosing a pagination strategy for APIs (OFFSET/LIMIT vs keyset)
• Selecting index types for query patterns (B-tree, GIN, hash, covering)
• Checking for common anti-patterns before deploying queries

Best practices

• List explicit columns instead of SELECT * to reduce I/O and prevent unexpected schema changes
• Prefer range filters (date >= ...) over functions on columns to allow index use
• Use NOT EXISTS instead of NOT IN when NULLs may be present
• Favor keyset pagination for large offsets; use OFFSET/LIMIT only for small, simple pages
• Create covering indexes for frequent SELECT columns to avoid table lookups

Example use cases

• Generate an active user list, then join aggregated order counts via chained CTEs
• Compute running totals and previous-period comparisons using LAG/ROW_NUMBER/SUM OVER
• Paginate product lists in an API using keyset pagination for high-offset performance
• Choose a GIN index for JSONB array searches and a B-tree for ORDER BY and range filters
• Replace N+1 queries by joining or batching related rows in a single query