Marketplace sql-patterns

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.

install

source · Clone the upstream repo

git clone https://github.com/aiskillstore/marketplace

Claude Code · Install into ~/.claude/skills/

T=$(mktemp -d) && git clone --depth=1 https://github.com/aiskillstore/marketplace "$T" && mkdir -p ~/.claude/skills && cp -r "$T/skills/0xdarkmatter/sql-patterns" ~/.claude/skills/aiskillstore-marketplace-sql-patterns && rm -rf "$T"

manifest: skills/0xdarkmatter/sql-patterns/SKILL.md

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