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

1 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.