SQLCraft v1.0.0

First stable release of SQLCraft — a collection of PostgreSQL queries (100% SQL, no application code) going from basic joins to advanced SQL and measured query optimisation, on a single realistic e-commerce schema.

Quick start

No local PostgreSQL needed — just Docker.

docker compose up -d
docker compose exec db psql -U sqlcraft -d sqlcraft -f /sql/00-schema.sql
docker compose exec db psql -U sqlcraft -d sqlcraft -f /sql/01-seed.sql

Then run the query files in order (02 → 05). Full instructions in the README.

What's inside

File	Contents
00-schema.sql	5-table e-commerce schema with full PK/FK/NOT NULL/UNIQUE/CHECK constraints
01-seed.sql	~26k rows generated with generate_series (500 customers, 300 products, 4,000 orders)
02-basics.sql	Joins, aggregation, GROUP BY / HAVING, revenue per customer, top products, per-country and per-month breakdowns
03-window-functions.sql	RANK/DENSE_RANK, running totals with SUM() OVER, LAG/LEAD month-over-month growth, ROW_NUMBER top-N per group
04-cte-recursive.sql	Recursive CTEs walking the category tree up (breadcrumb) and down (full subtree)
05-optimisation.sql	Real EXPLAIN ANALYZE before/after on a missing FK index

The optimisation result

The headline of the project — a genuinely measured improvement, not an assumed one:

Metric	Before	After
Scan on order_items	Seq Scan — 26,261 rows	Index Scan — 5 rows × 5 loops
Join strategy	Hash Join	Nested Loop
Buffers	187	28
Execution time	4.387 ms	0.227 ms (~19× faster)

Both plans were captured from the same database state, and are pasted into the file as annotated comments explaining why each node changed.

Documentation

• README — quick start, per-query summary, project rationale
• Schema diagram — Mermaid ER diagram of the 5 tables