Every lesson, every exercise

Week 1 — Foundations, properly

• 1.1 Joins are sets (38 min) — inner, left, full, anti- and semi-joins; drawing them; the three bugs every left join hides.
• 1.2 NULL, GROUP BY and HAVING (41 min) — three-valued logic, aggregates that ignore NULL, and the "column must appear in GROUP BY" error explained once and for all.
• Exercises: 12 queries against the shop dataset. Solutions with commentary.

Week 2 — Modelling

• 2.1 Keys and constraints (35 min) — natural vs. surrogate keys, unique constraints as documentation, foreign keys you can trust.
• 2.2 Normal forms without the theory (44 min) — one rule of thumb, three worked examples, and when to break it.
• Project: design the reporting schema you'll use for the rest of the course.

Week 3 — Window functions

• 3.1 Ranking and running totals (40 min) — `ROW_NUMBER`, `RANK`, `DENSE_RANK`, `SUM() OVER`, and the frame clause.
• 3.2 Gaps, islands and lead / lag (42 min) — sessionisation, streaks, and "compared to last month".
• Exercises: 10 real analytics questions.

Week 4 — Query plans and indexes

• 4.1 Reading EXPLAIN (39 min) — seq scan vs. index scan, nested loop vs. hash join, and the one number to look at first.
• 4.2 Indexes (43 min) — B-tree, composite column order, covering indexes, partial indexes, and when not to.
• Exercise: make five slow queries fast. Two of them without an index.

Week 5 — Refactoring

• 5.1 CTEs and views (36 min) — naming, ordering, and when a CTE hurts performance.
• 5.2 Live refactor (cohort only) — bring your ugliest real query.

Week 6 — Production habits

• 6.1 Migrations and testing (40 min) — reversible migrations, testing queries with fixtures, and catching regressions.
• 6.2 The checklist (25 min) — what to check before any query ships. Printable.

Certificate of completion issued to cohort students who submit five of six exercise sets.