CtrlK
BlogDocsLog inGet started
Tessl Logo

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 with Tessl CLI

npx tessl i github:NeverSight/skills_feed --skill sql-patterns
What are skills?

96

Review — 95%

Does it follow best practices?

Validation — 11 / 11 Passed

Validation for skill structure

SKILL.md
Review
Evals

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)

FunctionUse
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() OVERRunning total
AVG() OVERMoving 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

TypeReturns
INNER JOINOnly matching rows
LEFT JOINAll left + matching right
RIGHT JOINAll right + matching left
FULL JOINAll 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 TypeBest For
B-treeRange queries, ORDER BY
HashExact equality only
GINArrays, JSONB, full-text
CoveringAvoid table lookup

Anti-Patterns

MistakeFix
SELECT *List columns explicitly
WHERE YEAR(date) = 2024WHERE date >= '2024-01-01'
NOT IN with NULLsUse NOT EXISTS
N+1 queriesUse 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
Repository
NeverSight/skills_feed
Commit

5c15b3d

Last updated
Created

Is this your skill?

If you maintain this skill, you can claim it as your own. Once claimed, you can manage eval scenarios, bundle related skills, attach documentation or rules, and ensure cross-agent compatibility.

Claim skill