with one click
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.
Menu
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.
Professional frontend standards for building, scaffolding, extending, or reviewing any UI or frontend project — new or existing — even when standards aren't explicitly asked for. Keeps generated code consistent, reusable, secure, and production-quality. Framework-agnostic: React, Vue, Angular, Svelte, plain JS.
发布本地生成的 HTML、Markdown、TXT、PDF、Word 或 PPTX 到 ShareOne 平台,生成公网分享短链接;或者当用户提供 ShareOne 链接并要求下载文件、修改文件、拉取/处理评论时使用此技能。当用户要求“发布”、“分享”、“生成链接”、“上线”,或者“下载这个链接的文件”、“修改这个 ShareOne 链接的内容”、“拉取这个链接的评论”时,必须使用此技能。
Generate AI chat completions using GPT-4o through the verging.ai proxy API with streaming (SSE) and non-streaming response support.
Convert text to speech audio using OpenAI TTS-1-HD through the verging.ai proxy API. Supports multiple voices, playback speed control, and various audio output formats.
Generate AI images using DALL-E 3 or gpt-image-1 through the verging.ai proxy API. Supports standard and HD quality, multiple images per request, and returns CDN-hosted image URLs.
Analyze images using GPT-4o Vision through the verging.ai proxy API, supporting both image URL (JSON) and file upload (multipart) modes.
| 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 |
Quick reference for common SQL patterns.
WITH active_users AS (
SELECT id, name, email
FROM users
WHERE status = 'active'
)
SELECT * FROM active_users WHERE created_at > '2024-01-01';
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;
| 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;
| 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 |
-- 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 Type | Best For |
|---|---|
| B-tree | Range queries, ORDER BY |
| Hash | Exact equality only |
| GIN | Arrays, JSONB, full-text |
| Covering | Avoid table lookup |
| 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 |
For detailed patterns, load:
./references/window-functions.md - Complete window function patterns./references/indexing-strategies.md - Index types, covering indexes, optimization