What is a CTE and why use it?

A Common Table Expression (CTE) names a subquery for readability and reusability, as shown in the CTE examples.

How do OFFSET/LIMIT and keyset pagination differ?

OFFSET/LIMIT is simple but slower on large offsets; keyset pagination queries use a WHERE clause on a known last id for scalable navigation.

Which index type should I use for my workload?

Use B-tree for range and ORDER BY, Hash for exact matches, GIN for arrays/JSONB/full-text, and consider covering indexes to avoid extra lookups.

sql-patterns

Scanned

npx machina-cli add skill aiskillstore/marketplace/sql-patterns --openclaw

Files (1)

SKILL.md

2.5 KB

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

Source

git clone https://github.com/aiskillstore/marketplace/blob/main/skills/0xdarkmatter/sql-patterns/SKILL.mdView on GitHub

Overview

A quick reference for common SQL patterns, including CTEs, window functions, joins, pagination, and indexing strategies. It helps developers write readable, efficient queries and make informed indexing choices.

How This Skill Works

The skill presents concrete examples and reference tables for CTE usage, chained CTEs, window function operations, various join types, and pagination strategies. It also links to indexing guidance to optimize query performance.

When to Use It

Refactor complex subqueries using CTEs for readability
Compute running totals or ranks with window functions
Combine multiple query blocks via INNER/LEFT/FULL joins
Implement pagination with OFFSET/LIMIT or keyset methods
Choose indexing strategies to support the workload (range queries, exact matches, JSON/array data)

Quick Start

Step 1: Review CTE, window, join, and pagination sections in the skill
Step 2: Implement a basic CTE and a chained CTE similar to the examples
Step 3: Choose a pagination strategy and an index type based on your data patterns

Best Practices

Name and organize CTEs clearly to simplify debugging
Prefer keyset pagination for large datasets over OFFSET for performance
Avoid SELECT *; select only needed columns
Choose index types appropriate for workload (B-tree for ranges, Hash for equality, GIN for JSONB/arrays)
Be mindful of N+1 queries; use joins or batch operations

Example Use Cases

Active users example using a CTE to filter status and a final selection
Chained CTEs to derive user order counts and join back to user data
Window function example computing a running total of revenue
Pagination examples: OFFSET/LIMIT and keyset pagination
Index guidance including B-tree, Hash, GIN and covering indexes

Frequently Asked Questions

Add this skill to your agents