Advanced Patterns

Advanced Patterns

Hierarchies and graphs, JSON in SQL, pivoting/unpivoting, and time-series patterns — the topics that don't fit cleanly in other modules but show up regularly in production.

Suggest an edit

Advanced Patterns

The SQL toolkit is mostly composed by Module 9. This last module covers four patterns that don't fit cleanly elsewhere but show up routinely in real production work.

Place in the curriculum

  • Prerequisites: comfort with the rest of the curriculum, especially CTEs and window functions.

Chapters

  1. Hierarchies and Graphs — adjacency lists, closure tables, materialised paths, ltree (Postgres). When recursion isn't enough.
  2. JSON in SQL — JSONB, indexing JSON paths, when to use JSON vs columns.
  3. Pivoting and Unpivoting — turning rows into columns and vice versa.
  4. Time-Series Patterns — bucketing, gap-filling, retention windows, the patterns specific to time-stamped data at scale.
Mark as read