From the Editor — Vol. I, No. 1
A field guide to writing
PostgreSQL by hand,
one query at a time.
Set against a single eleven-table schema, this is a working handbook of 756 hand-checked queries — from a humble SELECT to a recursive CTE walking an organisation chart. Read it cover to cover, or open the page you need.
I.
Read more than you write.
Every query in this volume is set in proper monospace, indented for the eye, and annotated where a junior reader would stumble.
II.
The schema is not an appendix.
Tables, keys, and relationships sit beside the page. You should never have to leave the chapter to remember a column.
III.
No icons, no glow, no theatre.
Ink on parchment, rules where rules belong, oxblood for emphasis. The query is the picture.
A specimen page
How a single answer is set on the page.
Each entry begins with the question in plain English, followed by the query — typeset, syntax-coloured, and copyable in one motion. Beneath it, a brief note on why this clause and not another, and the tables it touches, linked back to the schema.
- ·Window functions used where they actually belong.
- ·CTEs over nested subqueries when a human will read it.
- ·Time zones handled, not hoped for.
-- The five top-spending customers in the last quarter,
-- with a running share of total revenue.
SELECT
c.id,
c.name,
SUM(o.total) AS spent,
SUM(SUM(o.total)) OVER (ORDER BY SUM(o.total) DESC
ROWS UNBOUNDED PRECEDING)
/ SUM(SUM(o.total)) OVER () AS running_share
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.placed_at >= date_trunc('quarter', now()) - interval '3 months'
GROUP BY c.id, c.name
ORDER BY spent DESC
LIMIT 5;How to read this book
01
Pick a chapter.
Twelve chapters, arranged from foundations to performance. Independent — open whichever you need.
Browse chapters →02
Read the query.
Every answer is hand-set with a short note on intent, edge cases, and the alternative you didn't take.
See a sample →03
Check the schema.
Eleven tables, fully indexed and cross-referenced. Each query lists the tables it touches.
Open the schema →
The schema in plain view
Eleven tables. One mental model.
employees
10 columns
PK emp_id · 2 FK
departments
5 columns
PK dept_id · 0 FK
salary_history
6 columns
PK history_id · 1 FK
projects
7 columns
PK project_id · 1 FK
project_assignments
6 columns
PK assign_id · 2 FK
customers
6 columns
PK cust_id · 0 FK
products
6 columns
PK product_id · 1 FK
orders
6 columns
PK order_id · 1 FK
“A query well written is a sentence well thought. The database does not reward cleverness — it rewards clarity.”
— The Editor
What readers say
“I keep this open in a tab. When I need a window function or a CTE I can read, not copy-paste blindly, I come here first.”
M. L. Chen
Senior data engineer, San Francisco
“Finally a SQL reference that treats the schema as part of the answer, not an afterthought in a footnote.”
A. R. Okonkwo
Backend lead, Berlin
“No noise, no gradients, no dark-mode toggle that breaks your eyes. Just queries, well set, with context. Exactly what I wanted.”
J. Sørensen
Freelance analyst, Copenhagen
Start here — one query per chapter
All chapters- 01JSONB: store and query JSON dataadvancedIntermediate
- 02Select employees using CASE to label salary bracketsbasicIntermediate
- 03CTE for deduplication using ROW_NUMBERcteIntermediate
- 04AGE: calculate time differencedatetimeIntermediate
- 05HAVING: departments with budget over 1 millionaggregationIntermediate
- 06UPDATE with JOIN (UPDATE FROM syntax)dmlIntermediate
- 07FULL OUTER JOIN: all employees and departmentsjoinsIntermediate
- 08GIN index for full-text and array searchperformanceIntermediate
- 09SPLIT_PART: extract part of delimited stringstringsIntermediate
- 10Correlated subquery: find department average for each employeesubqueriesIntermediate
- 11Find employees whose salary is above averagefilteringIntermediate
- 12SUM OVER: running total of orderswindowIntermediate
The chapters
Twelve · arranged alphabetically- 01350 queriesRead →
Advanced PostgreSQL
Beyond the standard surface. JSONB, arrays, full-text search, partitions, and the features that make Postgres feel limitless.
- 02136 queriesRead →
Basic SELECT
The foundation. Selecting columns, ordering, limiting, and counting — the vocabulary every query is built from.
- 0315 queriesRead →
CTEs & Recursive
Common table expressions. Named building blocks, recursion, and the readable alternative to nested subqueries.
- 0416 queriesRead →
Date & Time Functions
Time, done properly. Intervals, truncation, extraction, time zones, and the arithmetic that survives DST.
- 0524 queriesRead →
GROUP BY & Aggregation
Folding rows into summaries. GROUP BY, HAVING, ROLLUP, and the aggregate functions that turn data into answers.
- 0615 queriesRead →
INSERT, UPDATE, DELETE
Changing data safely. INSERT, UPDATE, DELETE, UPSERT, RETURNING, and the patterns that keep history intact.
- 0732 queriesRead →
JOINs (INNER, LEFT, RIGHT, FULL)
Combining tables. Inner, left, right, full, self, and the cross — knowing which one to reach for is most of the craft.
- 0876 queriesRead →
Performance & Indexes
Making queries fast. Indexes, EXPLAIN, statistics, partial and expression indexes, and reading a plan without flinching.
- 0919 queriesRead →
String Functions
Text in Postgres. Concatenation, casing, trimming, regex, and the functions that quietly do most of the work.
- 1021 queriesRead →
Subqueries & EXISTS
Queries inside queries. Scalar, correlated, EXISTS, IN, ANY, ALL — and when a CTE would read better.
- 1130 queriesRead →
WHERE & Filtering
Narrowing the result set. Predicates, boolean logic, pattern matches, null handling, and the small operators that change everything.
- 1222 queriesRead →
Window Functions
Looking sideways without collapsing rows. Partitioning, ordering, running totals, ranks, leads, and lags.
Begin reading