Sql patterns

Skill ComeOnOliver/skillshub/skills/aiskillstore/marketplace/0xdarkmatter/sql-patterns

🧠 The right skill, one API call. AI agent skills registry with token-efficient skill resolution. 5,000+ skills from 500+ top repos.

Install

npx -y skills add ComeOnOliver/skillshub --skill sql-patterns

Assembled from the repository path, not quoted from the project. Check it against their README if it does not work.

What its author says it does

Copied from the file, not written here

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.

SKILL.md

2.5 KB, as published. Nobody here has run it

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