Created by
jrz1977
RSS Feed View All Collections by jrz1977
Mastering CTEs (Common Table Expressions)

5 books • PUBLIC

Learn to write clearer, more maintainable SQL queries with Common Table Expressions (CTEs). This collection shows practical examples of using CTEs to break down complex queries into logical steps, from simple filtering and aggregation to advanced patterns like recursive queries and gaps-and-islands problems. Each example includes working code you can run immediately in PostgreSQL, MySQL, Oracle, or MariaDB—no signup required. Whether you're analyzing time-series data, building hierarchical reports, or optimizing query performance, CTEs are an essential SQL skill.

Open Access
Collection Books:
  1. sql-gaps-and-islands-problem
    Master the gaps-and-islands pattern, one of SQL's most versatile techniques for analyzing time-series data. This tutorial demonstrates how to identify continuous periods (islands) and breaks (gaps) in overlapping events, from hotel booking clusters to network alarm sequences. Learn to use window functions like LAG() and running sums with CTEs to solve real-world problems in network monitoring, user session analysis, server uptime tracking, and equipment maintenance scheduling. Includes complete working examples you can run immediately in PostgreSQL or MySQL.
  2. Tracing Paths with Recursive CTEs
    Learn to reconstruct complete paths from individual point-to-point records using recursive CTEs, one of SQL's most powerful features for connected data. This tutorial demonstrates how to trace end-to-end journeys through linked records, from package delivery routes to network packet paths. Originally developed for analyzing IPFIX network flow data, this pattern applies universally to organizational hierarchies, manufacturing bill-of-materials, supply chains, file systems, and any scenario where you need to follow parent-child relationships. Includes complete working examples you can run immediately in PostgreSQL or MySQL at SQLBook.io.
  3. SQL for Flow Diagrams-Following the Money
    Master the SQL pattern for analyzing flows between entities. Aggregate transactions, rank by volume, and generate visual flow diagrams. Works for budget tracking, network traffic, supply chains, API calls, and more.
  4. Tree Traversal in SQL-The Parent-Child Pattern
    Master recursive CTEs for hierarchical data. Trace paths up and down supply chains, calculate aggregate metrics across branches, and identify critical nodes. One table, one foreign key, infinite possibilities.
  5. Ranking Within Hierarchies-The Bottom-Up Rollup Pattern
    Combine recursive aggregation with window function ranking to create hierarchical reports. Calculate totals that include all descendants, rank siblings by their rolled-up metrics, and maintain tree order. Works for budgets, traffic reports, sales territories, and more.