aiskillstore/marketplace / skills/0xdarkmatter/sql-patterns
sql-patterns
VerifiedQuick 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.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 2.5 KB |
| references/indexing-strategies.md | markdown | 7.0 KB |
| references/window-functions.md | markdown | 6.9 KB |
| skill-report.json | config | 40.5 KB |
SKILL.md
View on GitHub---
name: sql-patterns
description: "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."
allowed-tools: "Read Write"
---
# SQL Patterns
Quick reference for common SQL patterns.
## CTE (Common Table Expressions)
```sql
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
```sql
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 |
```sql
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
```sql
-- 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
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |