Gaps and Islands

Imagine we want to analyze customer behavior over time, for example, identifying periods when a customer made consecutive purchases or spotting breaks in order activity. These insights can help us detect patterns like consistent engagement or customer churn. This is where the Gaps and islands pattern shines. In this lesson, we’ll learn how to identify both “gaps” (missing records in a sequence) and “islands” (continuous groups of related rows).

We'll understand when to use this pattern, how to build it step by step, and practice using real data from our course database.

By the end of this lesson, we’ll be able to:

• Detect missing sequences or skipped dates in ordered datasets.

• Group consecutive events into logical "islands."

• Understand the subtle difference between detecting inactivity versus grouping active periods.

• Write efficient and readable SQL queries to solve the Gaps and Islands problems.

Pattern overview

Category:

• Sequencing & Hierarchical Patterns

Intent:

To detect missing values in a sequence (gaps) or group continuous events (islands) in a time-ordered or ID-ordered dataset.

Motivation:

When working with logs, customer activity, sales timelines, or order histories, we often want to group related events together or detect inactivity. Gaps and Islands queries help us answer:

• When did customer engagement drop off?

• What were the uninterrupted periods of order activity?

• Are there any missing delivery dates?

Also known as:

• Consecutive range detection

• Sequence break detection

Structure

There are two flavors of this pattern:

1. Gaps: Identify missing records in a sequence (e.g., skipped dates or IDs).

2. Islands: Identify continuous blocks of activity or presence (e.g., streaks of purchases).

We often use:

• ROW_NUMBER() for comparing position-based ordering.

• Self joins or date arithmetic to detect breaks in sequences.

Keywords

ROW_NUMBER, LAG, LEAD, DATEDIFF, GROUP BY, PARTITION BY, MIN, MAX

Problem structure

We use the Gaps and Islands pattern when:

• We need to detect gaps, missing data points in a continuous sequence (e.g., days with no activity).

• We need to identify islands, consecutive records that form a continuous block (e.g., purchases made on back-to-back days).

• This pattern is useful when analyzing event streaks, data completeness, or uninterrupted sequences.

• Look for keywords like: “missing days,” “consecutive events,” “activity streaks,” “gaps in data,” or “continuous blocks” to identify when this pattern applies.

Example use cases

Detect missing order dates (Gaps)

Given the following structure of the Orders table:

Where OrderID is the primary key and CustomerID is the foreign key from the Customers table referring to the customer who placed the order.

The table contains information about the orders placed. Let’s say we expect orders to be placed every day, but we want to find dates with no orders. Write an SQL query to return all dates between ...