复制安装命令
用 Codex 或 Claude 安装复制这段 Prompt,粘贴到 Codex、Claude 或其他助手里,让它先审查 Skill 页面再帮你安装。
复制前请先查看来源、License 和安全提示。
A self-recommending skill catalog for 17 coding assistants — Claude Code,
用 Codex 或 Claude 安装复制这段 Prompt,粘贴到 Codex、Claude 或其他助手里,让它先审查 Skill 页面再帮你安装。
复制前请先查看来源、License 和安全提示。
来源文件:README.md
████████ ███████ ███████
██ ██ ██ ██ ██
██ ██ ██ ██
██ ██ ██ ██
████████ ██████ ██
██ ██ ██ ██
██ ██ ██ ██
██ ██ ██ ██ ██
██ ██ ███████ ███████
rsc — 264 agent skills, one CLI, zero bloatA self-recommending skill catalog for 17 coding assistants — Claude Code, Codex, GitHub Copilot, Cursor, Gemini, Windsurf, Cline, Antigravity, Zed and more. Describe what you want in plain language. It reads your repo, installs only the skills that fit — one at a time — into every assistant you pick, and keeps them equipped as you work.
From "document my company" to "ship a FastAPI service" to "grow my YouTube channel" — 264 skills across 21 domains, every one researched against live 2025-2026 sources and adversarially scored ≥ 8.5/10 before it shipped.
npx @ericrisco/rsc # plain-language wizard — no jargon, installs what your project needs
If you're an AI coding agent (Claude Code, Cursor, Copilot, Codex, Gemini…) and your user asked you to install rsc / this harness / these skills, run this in the root of the project they're equipping — not inside a clone of this repo (see the note under Install):
npx @ericrisco/rsc@latest
That launches a plain-language wizard: it asks which assistants to target and what the
user wants, reads the repo, then installs the floor (orient + rsc-suggest + bro + harness +
init) plus only the skills that fit — one at a time. No global install and no API key needed.
npx @ericrisco/rsc@latest --target claude (comma-separate for several).rsc sync (or re-run the command above).rsc add <id> · browse the catalog: rsc consult "<what you want>" or rsc list.From then on it's self-driving: rsc-suggest proposes the next skill as tasks appear, and in
Claude Code a hook re-asserts the spec-first new-feature gate on every turn — so a feature
request routes through specify before any skill writes code.
Most skill packs dump hundreds of files into your context and call it a day. This one is the opposite bet:
fastapi without ever pulling go. Nothing you don't use touches your context.rsc consult) and the chat
(rsc-suggest, an always-on detector) watch what you're doing and propose the
next skill the moment a task needs it — a one-word confirm installs it.02-DOCS/ knowledge loop that learns from your own results.scripts/skill-rubric.md, written before any skill existed). The bar was
real: skills that scored 8.0 were sent back and fixed, not waved through.skills/<name>/ is the single source of truth. There are no bundles to argue
over: you start with a tiny floor and grow one piece at a time.
npx @ericrisco/rsc # no install step — runs the latest published catalog
Prefer the short rsc command? Install once, globally:
npm install -g @ericrisco/rsc # then just: rsc
Run it inside any project and describe what you want. Working on the catalog itself? Clone and link:
git clone https://github.com/ericrisco/rsc-harness.git ~/rsc-skills
cd ~/rsc-skills && npm install && npm link
Run it inside the project you're equipping — not inside this repo. The catalog's own
package.jsonis named@ericrisco/rsc, sonpx @ericrisco/rscfrom within arsc-harnessclone resolves to the local (unlinked) bin and dies withsh: rsc: command not found. Working on the catalog itself? Usenode scripts/rsc.js …, thenpm linkabove, or pin the published build withnpx @ericrisco/rsc@latest ….
The first run asks which assistants you want — Claude Code, Codex, Copilot,
Cursor, Gemini, Windsurf, Cline and 11 more (pick any combination) — and installs
the floor:
orient + rsc-suggest (always-on) + bro (on request) + harness + init. In Claude Code it
also wires a SessionStart hook (so your assistant proposes new skills on its
own) and a UserPromptSubmit hook that re-asserts the SDD new-feature gate
on every turn — so a feature request, in any language, routes through specify
first, before any skill writes code. Opt out per project with .rsc/.no-feature-gate.
Everything stays in the project, and the real skill files are written
once to .rsc/skills/<id>/. Each assistant you pick gets a lightweight
symlink back to that shared base — no copy is duplicated across IDEs. (If the
filesystem can't symlink, it falls back to a real copy automatically.)
$ rsc
██████╗ ███████╗ ██████╗ ← animated gradient wordmark
██╔══██╗██╔════╝██╔════╝
██████╔╝███████╗██║
264 skills · one CLI · zero bloat
What do you want to do? ↑↓ move · enter select
❯ Base install — the essentials (orient + suggest + bro + harness + init)
Base + Spec-Driven Development — specify → plan → implement → ship
Pick skills by hand, by area
Pick by area and you get a checkbox list — ↑↓ to move, space to toggle, enter to confirm:
Languages: ↑↓ move · space toggle · a all · enter confirm
❯ ◉ typescript
◯ python
◉ go
◯ rust
Then it asks which assistants to install for — tick as many as you like:
Which assistants do you want to install for? space toggle · a all · enter confirm
❯ ◉ Claude Code (.claude/skills/) ⟵ detected here
◉ Codex CLI (AGENTS.md)
◯ GitHub Copilot (.github/copilot-instructions.md)
◯ Cursor (.cursor/rules/)
◉ Windsurf (.windsurf/rules/)
◯ Cline (.clinerules/)
…17 in total — Gemini, Antigravity, Zed, Continue, Roo, Amp, opencode, Jules, Junie, Kiro, Aider
It detects your stack, asks which assistants to install for (the one it found in your folder is pre-marked), installs only what you chose, then prints the exact next steps for Claude Code / Codex / Cursor / Gemini / Antigravity — and from there keeps proposing the skills a task needs.
rsc # plain-language wizard (recommended) — pick skills AND assistants
rsc add fastapi postgresdb # install specific skills, by name
rsc add youtube-api remotion-video # …grow a channel, edit with Remotion
rsc add fastapi --target claude,codex # install into several assistants at once
rsc install --profile minimal # the floor: orient + suggest + bro + harness + init
rsc install --profile core # floor + the full SDD workflow
rsc install --profile full # everything (all 264)
rsc install --profile full --without go
rsc consult "I want to launch a SaaS" # recommend only, no install
rsc registry refresh # write .rsc/skill-registry.{json,md}
rsc list # what rsc has installed
rsc doctor # health check (state, hook, counts)
rsc sync --target claude,codex # refresh managed skills/hooks from the current package version
rsc backups # list project-local snapshots
rsc restore latest --dry-run # preview restoring the newest snapshot
rsc restore <snapshot-id> # restore a project-local snapshot
rsc upgrade --dry-run # show npm upgrade + sync commands
rsc uninstall postgresdb --dry-run # preview a removal
rsc is an npm package, so updating is two steps — bump the package, then
re-sync what's already wired into your project:
npm install -g @ericrisco/rsc@latest # global install: pull the newest catalog
rsc sync # refresh managed skills + hooks (auto-detects your assistant)
Not sure what a bump touches? Preview the exact commands without writing anything:
rsc upgrade --dry-run # prints the npm install + rsc sync lines for your target
Running through npx (no global install)? There's nothing to upgrade —
npx @ericrisco/rsc@latest always fetches the latest published catalog; just run
rsc sync afterwards if the project already has skills installed.
Every sync snapshots the project first, so a bad update is always reversible:
rsc backups # list project-local snapshots
rsc restore latest --dry-run # preview restoring the newest
rsc restore <snapshot-id> # restore it
Two faces, one catalog (manifest.json):
rsc / rsc consult rank the catalog against your words
(multilingual TF-IDF blended with exact tag/id weights and intent synonyms), merge that with what they
detect in your repo, and expand via each skill's recommends.rsc-suggest is a tiny always-on skill. When a task would
benefit from a skill you don't have, it names it and (one-word confirm) runs
rsc add <id> for you. It's the floor — installed with every profile.Repo detection maps real signals to skills: package.json + next → nextjs;
go.mod → go; pyproject.toml → fastapi; *.sql/prisma/ → postgresdb;
Dockerfile/.github/ → docker/github-actions; and so on. An empty repo
just asks in plain language.
264 skills, grouped by what you're trying to do. Click any skill to read its
SKILL.md. It fires on its own when a task matches.
The front door and the workspace brain.
init · harness · orient · suggest · bro · author-skill · sdd-init
harness is the Karpathy chaos→knowledge engine — a
01-TOOLS/layer (one folder per provider, each with a workingtest_connection) and a02-DOCS/self-improving wiki. It governs software or a whole company. orient is the always-on compass that keeps a non-technical human oriented after every step. bro is installed with every profile and rewrites any answer in plain, natural language when the user asks — without making its full body always-on.
📦 The
02-DOCS/brain is now 100% Open Knowledge Format (OKF v0.1) conformantGoogle Cloud published the Open Knowledge Format — a vendor-neutral standard for portable, agent-readable knowledge — built on the same Karpathy LLM-wiki pattern our
02-DOCS/engine has used from day one. We independently converged on the same design, so adopting the standard cost almost nothing. As of now, every02-DOCS/wiki/is a valid, portable OKF bundle:
- Markdown + YAML frontmatter,
typeon every concept doc, OKF-standard fields (title,description,resource,tags,timestamp).- Standard markdown links (not wikilinks) form the knowledge graph — any OKF consumer reads it, and it stays a native Obsidian vault (graph, backlinks, Properties, Bases). Same files, no export step.
- Reserved files honored:
index.md(no frontmatter) for navigation,log.md(newest-first, ISO 8601) for history.Tarball a
wiki/and any OKF tool — including Google's own viewer — can read it. And the brain now keeps your repo clean: a loose file it ingests (a PDF at the root, anything ininbox/) is moved intoraw/, never left as clutter.
Take a fuzzy intent to a shipped, verified change — phase by phase. npx @ericrisco/rsc install --profile core.
sdd · constitution · idea-refinement · specify · clarify · plan · tasks · analyze · decision-challenge · implement · source-grounded-development · verify · review · simplify-code · ship · debug · worktrees · parallel
finance-ops · invoicing · bookkeeping · pricing · sales-pipeline · lead-gen · cold-outreach · proposals · contracts · customer-support · client-onboarding · retention · hiring · people-ops · inventory · logistics-ops · procurement · meeting-notes · sop-builder · project-ops
pitch-deck · investor-materials · financial-model · fundraising · unit-economics · grants
gdpr-privacy · terms-conditions · compliance · data-policy · ip-trademark
marketing · seo-geo · content-engine · social-publisher · brand-voice · brand-identity · newsletter · landing-copy · ads · article-writing · case-studies · video-shorts · podcast · market-research · competitor-watch · press-kit · community · webinar · review-management
Each with a 02-DOCS feedback loop that learns from your own results. remotion-video edits programmatically — transitions, Whisper captions, silence removal.
youtube-api · youtube-strategy · youtube-ideation · youtube-thumbnails · youtube-packaging · remotion-video · tiktok-api · instagram-api · shortform-strategy · shortform-ideation · shortform-packaging · shortform-editing · viral-score · linkedin-api · linkedin-strategy · linkedin-content · linkedin-carousels · linkedin-outreach · medium-writing · medium-publishing · medium-strategy
stripe · email-connector · google-workspace · notion-connector · whatsapp-telegram · automation-flows · api-connector-builder · webhooks · data-scraper · spreadsheet-ops · calendar-scheduling · document-processing · e-signature
Operate the big automation platforms programmatically or via MCP — create and manage automations dynamically, not just design them on a canvas. automation-strategy decides whether / what / which platform; the platform skills drive the live REST API or MCP server (harness connectors ship for each). Complements automation-flows (visual design + importable workflow JSON).
automation-strategy · n8n · make · zapier · power-automate
analytics · dashboard · kpi-framework · reporting · ab-testing · forecasting · data-cleaning · business-intelligence
building-agents · rag · embeddings-search · prompt-engineering · llm-pipeline · agent-eval · chatbot · ai-media · replicate-images · structured-extraction · agent-safety · cost-tracking
replicate · runpod · modal · huggingface · ollama · together-fireworks · fal
Train and adapt open models end to end: classic ML, deep learning, NLP, fine-tuning (with Unsloth), building training datasets, choosing open-weight models by license/size, and serving them at throughput with vLLM. Facts that move monthly (versions, model licenses) are verified at author time and hedged.
machine-learning · deep-learning · nlp · finetuning · training-data · unsloth · open-weights · vllm
typescript · python · java · csharp-dotnet · php · ruby · cpp · elixir · bash-scripting · sql · go
fastapi · nextjs · react · react-native · vue-nuxt · angular · svelte · astro · solid-js · htmx · nodejs · nestjs · django · laravel · rails · spring-boot · phoenix · flutter · swift-ios · kotlin-android · compose-multiplatform · expo · tauri · electron · rust · wordpress · shopify · no-code-app · chrome-extension · api-design
Three engines + engine-agnostic disciplines. Every engine skill pins the current version and bans deprecated APIs, so the agent stops emitting stale Godot-3 / legacy-Unity code.
godot · unity · unreal · game-design · game-storytelling · level-design · gamedev-shaders · gamedev-multiplayer · gamedev-physics · gamedev-pathing · gamedev-shipping
postgresdb · mysql · mongodb · redis · supabase · neon · planetscale · sqlite-turso · prisma-orm · drizzle-orm · firebase · dynamodb · vector-db · clickhouse-analytics · duckdb · db-migrations · backups
vercel · netlify · cloudflare · railway · render · fly-io · coolify · hetzner · digitalocean · aws-essentials · gcp-essentials
docker · github-actions · git-workflow · domains-dns · monitoring · email-deliverability · scaling · deployment · deprecation
code-review · security-scan · secure-coding · testing-py · testing-web · testing-go · e2e-testing · accessibility · performance · error-handling · observability
design · presentations · course-storytelling · course-builder · technical-writing · translation-l10n
knowledge-ops · codebase-onboarding · research-ops · decision-records · continuous-learning · skill-scout · context-budget · roast-me · fable-operator
skills/<name>/ is the catalog source. On install the real files land once
in the project at .rsc/skills/<id>/; each assistant you pick gets a symlink
(or a converted file) back to that shared base — pick several and nothing is
duplicated. The wizard asks which ones; --target a,b does it non-interactively.
| Target | Skill destination (→ .rsc/skills/<id>/) | Always-on detector |
|---|---|---|
claude | .claude/skills/<id>/ → symlink (copy on Windows) | SessionStart hook in .claude/settings.json |
codex | .codex/rsc/<id>/ → symlink | block in AGENTS.md |
copilot | .github/rsc/<id>/ → symlink | block in .github/copilot-instructions.md |
cursor | .cursor/rules/<id>.mdc (converted) | always-apply rule |
gemini | .gemini/rsc/<id>/ → symlink | block in GEMINI.md |
windsurf | .windsurf/rsc/<id>/ → symlink | rule in .windsurf/rules/rsc-suggest.md |
cline | .clinerules/rsc/<id>/ → symlink | rule in .clinerules/rsc-suggest.md |
antigravity | .antigravity/rsc/<id>/ → symlink | block in .antigravity/AGENTS.md |
zed | .zed/rsc/<id>/ → symlink | block in AGENTS.md |
continue | .continue/rsc/<id>/ → symlink | rule in .continue/rules/rsc-suggest.md |
roo | .roo/rsc/<id>/ → symlink | rule in .roo/rules/rsc-suggest.md |
amp | .amp/rsc/<id>/ → symlink | block in AGENTS.md |
opencode | .opencode/rsc/<id>/ → symlink | block in AGENTS.md |
jules | .jules/rsc/<id>/ → symlink | block in AGENTS.md |
junie | .junie/rsc/<id>/ → symlink | block in .junie/guidelines.md |
kiro | .kiro/rsc/<id>/ → symlink | doc in .kiro/steering/rsc-suggest.md |
aider | .aider/rsc/<id>/ → symlink | block in CONVENTIONS.md |
codex,zed,amp,opencodeandjulesall share the one rootAGENTS.md; the block is idempotent, so picking several writes it once.
Each skill is a directory under skills/<name>/ whose SKILL.md frontmatter
drives both triggering and the installer's recommendations:
---
name: my-skill
description: Use when [specific triggers]… Triggers: 'phrase', 'frase'. NOT x (that is sibling).
tags: [keyword, keyword] # what the consult advisor searches over
recommends: [sibling-skill] # what the system offers to install next
profiles: [core, full] # optional: named-profile membership
origin: risco
---
The full agent-skill spec lives at agentskills.io/specification.
skills/<name>/ is the single source of truth — every skill is authored
there, once. After editing any skill:
npm run manifest # regenerate manifest.json from skills/*/SKILL.md
npm run validate # ajv-validate frontmatter + check recommends integrity
npm test # unit + integration tests
bash scripts/eval-lint.sh # validate every skills/*/evals/cases.yaml
manifest.json is generated, never hand-edited; CI runs npm run manifest:check
and fails if it's stale or the skill count drifts. Adding a skill is: create
skills/<id>/SKILL.md with tags + recommends, run npm run manifest, done —
the rubric to hold it to is scripts/skill-rubric.md.
This is a personal catalog. Bug reports welcome via GitHub issues; PRs fixing detector patterns, provider endpoints, or typos are appreciated.
MIT. See LICENSE.
name: sql
description: "Use when writing or reviewing advanced SQL query logic independent of any one engine — multi-table joins, window functions, CTEs including recursive ones, GROUP BY and GROUPING SETS aggregation, and set operations — or when a query returns too many rows, too few, or wrong totals. NOT engine internals, indexes or EXPLAIN (that is `postgresdb`), NOT MySQL config (that is `mysql`), NOT OLAP columnar specifics (that is `duckdb`)."
tags: [sql, query, joins, window-functions, cte]
recommends: [postgresdb, mysql, duckdb, drizzle-orm]
origin: riscoThis skill is the portable query-writing layer that sits above any one database engine. It owns
the SELECT-side craft: joins and what each does to row count and NULLs, window functions
(PARTITION/ORDER/frame), CTEs (including recursive), aggregation (GROUP BY/GROUPING SETS/HAVING),
set operations (UNION/INTERSECT/EXCEPT), conditional logic (CASE/COALESCE/NULLIF), and the
NULL three-valued-logic traps that quietly corrupt results across every engine. You write queries a
reviewer accepts on Postgres, MySQL 8, SQLite, DuckDB, SQL Server, or BigQuery with minimal change, and
you flag exactly where a construct is non-portable and what the dialect substitute is. The target
standard is SQL:2023 (ISO/IEC 9075:2023), the ninth edition published June 2023; window functions
have been standard since SQL:2003, so they are safe to assume everywhere.
This is about thinking in sets and frames, not about one product's planner, DDL, indexing, or ops.
PARTITION BY vs GROUP BY, or ROWS vs RANGE
vs GROUPS frames.GROUP BY, HAVING, GROUPING SETS/ROLLUP/CUBE, conditional aggregates.UNION/INTERSECT/EXCEPT; deciding ALL vs distinct.NOT IN), or
wrong aggregates (counting joined duplicates).| The ask | Route to |
|---|---|
| Engine-level Postgres: DDL types, indexes, EXPLAIN, VACUUM, RLS, pooling | ../postgresdb/SKILL.md |
| MySQL-specific behavior/config (InnoDB, buffer pool) | ../mysql/SKILL.md |
| DuckDB local-analytics / columnar specifics | ../duckdb/SKILL.md |
| ClickHouse columnar OLAP engine specifics | ../clickhouse-analytics/SKILL.md |
| ORM/builder API ergonomics (the API, not the emitted SQL) | ../drizzle-orm/SKILL.md, ../prisma-orm/SKILL.md |
| Schema design / DDL / migrations | ../db-migrations/SKILL.md |
| BI dashboards, reporting layout, metric definitions | ../business-intelligence/SKILL.md |
| Cleaning messy data as a pipeline task | ../data-cleaning/SKILL.md |
The defining line: sql = portable query-language craft; engine skills = one product's behavior,
storage, and operations. When the engine isn't decided, or the question is "how do I express this in
SQL at all" rather than "how does Postgres run it" — you are in the right place.
JOIN syntax, never comma-joins. FROM a, b WHERE a.id = b.a_id hides the join
condition in the filter — drop the WHERE clause by accident and you get a silent cross product.SELECT id, name is ambiguous and breaks
the moment two joined tables share a column name; SELECT o.id, c.name survives schema changes.NOT EXISTS over NOT IN whenever the inner side is nullable. NOT IN returns zero rows if the
subquery yields a single NULL (3VL UNKNOWN is never TRUE); NOT EXISTS is NULL-safe. Standard, not
engine-specific.ORDER BY but no explicit frame defaults to
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which lumps tied rows together — not ROWS. This
silently wrong running total is the single most common window bug, identical across engines. Write the
frame explicitly.GROUP BY. Engines that let you skip it (old MySQL)
return an arbitrary row per group — a correctness landmine, not a convenience.UNION ALL unless you genuinely need dedup. Bare UNION sorts/hashes to remove duplicates — real
cost — and silently collapses rows you meant to keep. Add ALL by default; remove it deliberately.NULL = NULL is UNKNOWN, x <> 5 excludes
NULL x, and COUNT(col) skips NULLs while COUNT(*) does not. Decide what NULL means before the WHERE.| Want | Use | Row-count effect | NULL behavior |
|---|---|---|---|
| Only matching pairs | INNER JOIN | Can shrink and fan out on 1-to-many | Unmatched rows dropped |
| All left rows + matches | LEFT JOIN | ≥ left row count | Right columns NULL when no match |
| All rows from both | FULL JOIN | ≥ max(left, right) | NULLs on whichever side lacks a match |
| Every combination | CROSS JOIN | left × right (multiplies!) | None |
| "Left rows that have a match" | semi-join via EXISTS | = left, no duplication | No right columns added |
| "Left rows with no match" | anti-join via NOT EXISTS | ≤ left | NULL-safe (unlike NOT IN) |
A 1-to-many JOIN fans out the left row once per match. If you then SUM/COUNT, the aggregate is
inflated. Use a semi-join (EXISTS) when you only want existence, not the joined columns.
| You want… | Use | Result |
|---|---|---|
| One row per group (collapse detail) | GROUP BY | Fewer rows; only group keys + aggregates survive |
| Keep every row and add a per-group number | ... OVER (PARTITION BY …) | Same row count; aggregate alongside detail |
Rule of thumb: if the question is "per X, the total/rank/previous," and you still want the individual
rows, it is a window function. If you only want the rollup, it is GROUP BY.
ROWS / RANGE / GROUPS)| Frame unit | Counts by | Use for | Portability |
|---|---|---|---|
ROWS | Physical rows | Running totals, moving averages | Everywhere |
RANGE | Value range of the ORDER BY key | "All rows within ±N of this value/date" | Everywhere |
GROUPS | Peer groups (tied rows) | "N distinct ordering-value steps back" | Not in MySQL 8 |
ROWS and RANGE plus EXCLUDE and numeric RANGE offsets work on Postgres 11+ and SQLite 3.28+.
MySQL 8 supports only ROWS and RANGE — no GROUPS, no EXCLUDE. See references/window-functions.md.
| Need | Reach for |
|---|---|
| Existence / anti-existence test | correlated EXISTS / NOT EXISTS |
| Combine columns from another table | JOIN |
| Name an intermediate result, reuse or read it cleanly | CTE (WITH) |
| Hierarchy, graph walk, generated series | recursive CTE (WITH RECURSIVE) |
Every fence is sql. Full depth in references/.
Top-N per group — never LIMIT inside a correlated subquery.
-- Bad: correlated subquery runs once per customer; non-portable LIMIT placement
SELECT * FROM orders o
WHERE o.id IN (
SELECT id FROM orders i WHERE i.customer_id = o.customer_id
ORDER BY i.amount DESC LIMIT 3
);
-- Good: one pass, ranked, then filtered
SELECT customer_id, id, amount
FROM (
SELECT customer_id, id, amount,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
FROM orders
) ranked
WHERE rn <= 3;
Running total — make the frame explicit so ties don't lump.
-- Bad: no frame -> implicit RANGE, tied dates collapse into one running value
SELECT day, SUM(amount) OVER (ORDER BY day) AS running FROM sales;
-- Good: explicit ROWS frame counts physical rows
SELECT day,
SUM(amount) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM sales;
Period-over-period with LAG.
-- Good: previous row's value per partition; NULL on the first row is expected
SELECT month, revenue,
revenue - LAG(revenue) OVER (PARTITION BY product_id ORDER BY month) AS delta,
ROUND(100.0 * (revenue - LAG(revenue) OVER (PARTITION BY product_id ORDER BY month))
/ NULLIF(LAG(revenue) OVER (PARTITION BY product_id ORDER BY month), 0), 2) AS pct_change
FROM monthly_revenue;
NULLIF(prev, 0) guards against divide-by-zero; the first row's LAG is NULL by design.
Dedup keeping latest — QUALIFY is convenient but narrow.
-- Portable: rank then filter in an outer query
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC) AS rn
FROM users
) d WHERE rn = 1;
-- DuckDB / BigQuery / Snowflake only: QUALIFY skips the wrapper. NOT in Postgres/MySQL/SQLite.
SELECT * FROM users
QUALIFY ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC) = 1;
Recursive CTE with a depth guard — always bound the recursion.
-- Good: org chart walk; depth column stops runaway / cyclic graphs
WITH RECURSIVE tree AS (
SELECT id, manager_id, name, 1 AS depth
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.manager_id, e.name, t.depth + 1
FROM employees e JOIN tree t ON e.manager_id = t.id
WHERE t.depth < 50 -- hard ceiling; for true cycles track a path array
)
SELECT * FROM tree;
Conditional aggregation / pivot — FILTER reads cleaner than CASE.
-- Portable everywhere: CASE inside the aggregate
SELECT region,
SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid,
SUM(CASE WHEN status = 'open' THEN amount ELSE 0 END) AS open
FROM invoices GROUP BY region;
-- Postgres/SQLite/DuckDB: FILTER is the standard, more readable form. NOT in MySQL/SQL Server.
SELECT region,
SUM(amount) FILTER (WHERE status = 'paid') AS paid,
SUM(amount) FILTER (WHERE status = 'open') AS open
FROM invoices GROUP BY region;
GROUPING SETS / ROLLUP — one scan, multiple aggregation levels.
-- Good: subtotals per (region, product), per region, and grand total in one query
SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY ROLLUP (region, product); -- = GROUPING SETS ((region,product),(region),())
Anti-join via NOT EXISTS — the NULL-safe "rows with no match."
-- Good: customers who never ordered; correct even if orders.customer_id has NULLs
SELECT c.id, c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
The NOT IN-NULL footgun.
-- Bad: if ANY returned customer_id is NULL, this yields ZERO rows, silently
SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);
-- Good: NOT EXISTS, or NOT IN with an explicit IS NOT NULL filter on the inner column
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
| Construct | Notes |
|---|---|
QUALIFY | DuckDB / BigQuery / Snowflake only — elsewhere wrap in a subquery and filter rn |
FILTER (WHERE …) | Postgres / SQLite / DuckDB — MySQL & SQL Server need CASE |
GROUPS frame, EXCLUDE | Postgres 11+, SQLite 3.28+ — not in MySQL 8 |
EXCEPT | Standard; Oracle spells it MINUS |
| Row limiting | LIMIT … OFFSET (Postgres/MySQL/SQLite/DuckDB) vs FETCH FIRST n ROWS ONLY (standard/SQL Server 2012+) vs TOP n (SQL Server) |
| Set-op column match | By position and type, not by name — order your columns identically |
Full six-engine matrix in references/portability.md.
| Rationalization | Reality | STOP |
|---|---|---|
"NOT IN is clearer than NOT EXISTS" | One NULL in the inner set returns zero rows, silently | Use NOT EXISTS for nullable inner columns |
"SELECT * is fine in this query" | Hides which columns matter; breaks GROUP BY, ambiguous on joins | Project explicit, qualified columns |
| "Old MySQL let me skip the GROUP BY column" | You get an arbitrary row per group | List every non-aggregated column |
| "No frame needed, I just want a running sum" | Implicit RANGE lumps tied rows -> wrong total | Write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW |
| "I'll loop in app code and query per row" | N+1 round trips; a window function does it in one scan | Express it as one set-based statement |
"UNION to merge these results" | Pays a dedup sort and drops rows you wanted | UNION ALL unless dedup is the goal |
"COUNT(*) after the join is the real count" | A 1-to-many join fanned the rows out | Count on the base table or use a semi-join |
"Add DISTINCT to fix the duplicate rows" | Masks a fan-out join instead of fixing it | Find the join multiplying rows; fix the grain |
Run scripts/verify.sh from your project root. It is read-only, never connects to a database, and runs
on stock macOS bash 3.2. It heuristically scans discovered .sql files and warns on the footguns above
(NOT IN (SELECT …), comma-joins with WHERE-join predicates, SELECT * alongside GROUP BY, window
OVER (… ORDER BY …) with no explicit frame) and, if sqlfluff is installed, lints with --dialect ansi.
It exits non-zero only on a real sqlfluff lint error or unbalanced parens/quotes (dollar-quote aware);
every heuristic is advisory [warn], and an empty target passes clean.
references/window-functions.md — ranking/offset/aggregate-over catalog, every frame unit worked, EXCLUDE, named windows, implicit-frame trap, per-engine matrix.references/joins-and-sets.md — every join type with row-count reasoning, semi/anti/lateral joins, set ops + ALL/dedup/MINUS, the fan-out-inflates-aggregates bug.references/ctes-and-recursion.md — CTE structuring, recursive template (hierarchy/graph/series) with cycle + depth guards, the optimization-fence portability note.references/portability.md — full dialect matrix across Postgres / MySQL 8 / SQLite / DuckDB / SQL Server / BigQuery.../postgresdb/SKILL.md, ../mysql/SKILL.md, ../duckdb/SKILL.md, ../clickhouse-analytics/SKILL.md, ../drizzle-orm/SKILL.md, ../prisma-orm/SKILL.md, ../db-migrations/SKILL.md. ORM/engine internals are out of scope here — this skill owns the SQL those tools ultimately emit.
评论 (0)
暂无评论,成为第一个评论者吧!