ReddRadar
Agent skill
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 this agent skill to your Project
npx add-skill https://github.com/aiskillstore/marketplace/tree/main/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
Recommended Agent Skills
Expand your agent's capabilities with these related and highly-rated skills.
aiskillstore/marketplace
perigon-backend
Perigon ASP.NET Core + EF Core + Aspire conventions
aiskillstore/marketplace
perigon-agent
Pointers for Copilot/agents to apply Perigon conventions
aiskillstore/marketplace
perigon-angular
Angular 21+ standalone/Material/signal conventions for Perigon WebApp
aiskillstore/marketplace
fastapi-mastery
Comprehensive FastAPI development skill covering REST API creation, routing, request/response handling, validation, authentication, database integration, middleware, and deployment. Use when working with FastAPI projects, building APIs, implementing CRUD operations, setting up authentication/authorization, integrating databases (SQL/NoSQL), adding middleware, handling WebSockets, or deploying FastAPI applications. Triggered by requests involving .py files with FastAPI code, API endpoint creation, Pydantic models, or FastAPI-specific features.
aiskillstore/marketplace
context7-efficient
Token-efficient library documentation fetcher using Context7 MCP with 86.8% token savings through intelligent shell pipeline filtering. Fetches code examples, API references, and best practices for JavaScript, Python, Go, Rust, and other libraries. Use when users ask about library documentation, need code examples, want API usage patterns, are learning a new framework, need syntax reference, or troubleshooting with library-specific information. Triggers include questions like "Show me React hooks", "How do I use Prisma", "What's the Next.js routing syntax", or any request for library/framework documentation.
aiskillstore/marketplace
browser-use
Browser automation using Playwright MCP. Navigate websites, fill forms, click elements, take screenshots, and extract data. Use when tasks require web browsing, form submission, web scraping, UI testing, or any browser interaction.
Didn't find tool you were looking for?