🖼️SQL examples gallery

Read grain before you copy

Worked examples anchor pattern memory, read each query for grain, keys, and which relational operators are composed. Every example implies a schema and keys; name the driving relation and what duplicates would mean if joins were wrong.

Paste into EXPLAIN (or your engine's equivalent) so cardinality estimates show when toy data lies. Build a team snippet library with tags (dedupe, gaps, cohort, funnel) instead of one-off Slack SQL.

Short, copy-friendly patterns you will reuse in analytics and services.

🏆Patterns

Annotate dialect + version on every snippet you ship internally.

SQL

Top-N per partition

SELECT department_id, employee_id, salary
FROM (
  SELECT department_id, employee_id, salary,
         ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn <= 3;

✓Illustrative result set

department_id	employee_id	salary
10	501	185000
10	512	172000
10	503	160000

SQL

Safe divide

SELECT SUM(amount) / NULLIF(COUNT(*), 0) AS avg_amount FROM payments;

✓Illustrative result set

avg_amount
44.52

SQL

Half-open time window

SELECT COUNT(*) FROM events
WHERE ts >= TIMESTAMPTZ '2026-04-01'
  AND ts < TIMESTAMPTZ '2026-05-01';

✓Illustrative result set

count
128844

Gallery risks

Copy-pasting without adapting keys to your schema.
Assuming LIMIT makes a query cheap, it still scans unless ordered/indexed.
Examples that hide required indexes or session settings.

Make examples honest

Annotate each snippet with dialect and version.
Pair snippets with minimal CREATE TABLE fixtures.
Prefer CTEs in teaching materials for readability.
Re-run examples after major optimizer upgrades.

Plans vs toy data

Small example row counts can hide bad join orders, when you paste into a real catalog, compare estimated vs actual rows on the hottest join.

TL;DR

This gallery is a curated set of runnable patterns, each snippet is correct for teaching shape, but performance and dialect details still depend on your engine and data volume. Treat examples like starter templates: always run EXPLAIN, add indexes and constraints for production grain, and adapt identifiers/types to your schema before pasting into a shared database.