Lessons
Window functions: RANK, LAG, running totals
RANK, DENSE_RANK, ROW_NUMBER, SUM OVER, AVG OVER, LAG, LEAD, NTILE, and PARTITION BY.
Prerequisite: Practitioner track complete
What to look for:
- OVER() without PARTITION: operates on all rows
- PARTITION BY = GROUP BY without collapsing rows
- RANK vs DENSE_RANK: RANK has gaps for ties, DENSE_RANK does not
- LAG/LEAD: access previous/next row values — useful for deltas
CTEs and recursive queries
WITH clause for readable queries, multiple CTEs, and recursive CTEs for hierarchy traversal.
Prerequisite: Lesson 1 of Advanced
What to look for:
- CTE = named subquery, defined before the main query
- Multiple CTEs: WITH a AS (...), b AS (...) SELECT...
- Recursive CTE: base case UNION ALL recursive case
- Use recursion for org charts, file trees, category hierarchies
Transactions and ACID
BEGIN/COMMIT/ROLLBACK, SAVEPOINT, the four ACID properties, and isolation levels.
Prerequisite: Lesson 2 of Advanced
What to look for:
- Atomicity: all operations succeed or all are rolled back
- Always COMMIT or ROLLBACK — never leave a transaction open
- SAVEPOINT for partial rollback within a transaction
- Isolation levels: READ COMMITTED (default in PostgreSQL) vs SERIALIZABLE
Indexes and EXPLAIN ANALYZE
B-tree indexes, composite indexes, partial indexes, and reading EXPLAIN ANALYZE output.
Prerequisite: Lesson 3 of Advanced
What to look for:
- CREATE INDEX — dramatically speeds up WHERE on large tables
- Composite index: column order matters — most selective first
- EXPLAIN ANALYZE: Seq Scan (no index) vs Index Scan (index used)
- Partial index: index only a subset of rows (e.g. WHERE status = pending)
Schema design and normalization
First, second, and third normal forms, primary and foreign keys, and designing a real-world schema.
Prerequisite: Lesson 4 of Advanced
What to look for:
- 1NF: atomic values, no repeating groups
- 2NF: no partial dependency on composite key
- 3NF: no transitive dependency
- When to denormalize: read-heavy workloads, reporting databases, JSONB for flexible schemas
How to use this track: Each lesson links to the Codex SQL reference page. Use the reading level tabs. Use the "What to look for" bullets. Practice each query in a PostgreSQL session — try db-fiddle.com for a free browser SQL environment.
Track quiz
4 questions to check your understanding.
What is the difference between RANK() and DENSE_RANK()?
What does a recursive CTE require?
What does EXPLAIN ANALYZE show that EXPLAIN alone does not?
What is Third Normal Form (3NF)?